search function or similar

  • Thread starter Thread starter kevin
  • Start date Start date
K

kevin

hi

i want to be type a file path in a cell eg

\\GED\Mar2004\20040301.xls

from this cell i need a worksheet function to determine
the file name and file directory

so file name = 20040301.xls
file directory = \\GED\Mar2004\


I was thinking of something like a search function to find
the last "\" and from there using left and right worksheet
functions I could break the path in 2. But the Search
function starts from the right not the left so this will
not work - cna anyone think of something similar


thanks in advance
 
The search function starts from the left in my version of Excel.

You didn't give many examples of what you want to type in (and if you are
actually typing it, why put the \\ at the beginning?) which would help
someone in put something useful together.

You can always use FIND("\",A1,FIND("\",A1)+1) to find the 2nd \.. and you
can build it up to find the 3rd or 4th. That's probably what I'd use to
break the text up but maybe someone else has an idea.

There is always text-to-columns by "\" if it's a one time thing.
 
Hi

Maybe you'll be more detailed about what you want to do? Are you typing some
full file nime(s), not connected with workbook you are working with, ito
some cell(s), or you want the name of this workbook to be returned?

When last is the case, then there is no need for entering the file full nime
into cell - save the workbook (NB! It was important) and enter the next
formula into any cell
=MID(CELL("filename",Sheetname!A1),FIND("]",CELL("filename",Sheetname!A1))+1
,255)

When you really are enterin full file names into some range, then simply
split entries and enter path and file name into adjacent cells. You can
always concatenate them whenever you need it.
 
Hi
try the following formulas:

1. Filename:
=MID(TRIM(A1),FIND("^",SUBSTITUTE(TRIM(A1),"\","^",LEN(TRIM(A1))-LEN(SU
BSTITUTE(A1,"\",""))))+1,1024)

2. Directory
=LEFT(TRIM(A1),FIND("^",SUBSTITUTE(TRIM(A1),"\","^",LEN(TRIM(A1))-LEN(S
UBSTITUTE(A1,"\","")))))
 
Complete overkill, but if you don't like to use dummy characters:

=LEFT(A1,MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)="\")*ROW(INDIRECT("A1:
A"&LEN(A1)))))

and

=RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)="\")*ROW(INDI
RECT("A1:A"&LEN(A1)))))

Both are array formulas that need to be entered with <Ctrl> <Shift> <Enter>.
 
Thanks Vasant - this works perfectly

Basically
-----Original Message-----
Complete overkill, but if you don't like to use dummy characters:
(A1))),1)="\")*ROW(INDI
RECT("A1:A"&LEN(A1)))))

Both are array formulas that need to be entered with
Click to expand...
 
Back
Top