Bringing back info from a field

S

scfisher_list

I have a column, each row containing the directory structure and
filename.

ie

H:\ABC\98\EFG.zip
H:\ABC\98\HI.doc
H:\KLM\NO\PQRST\W.xls

I would like to bring back the directory in one adjacent column and the
filename in another column.
So example above i would end up with one column

H:\ABC\98
H:\ABC\98
H:\KLM\NO\PQRST

AND in another column
EFG.zip
HI.doc
W.xls



Is it possible using functions or VBA?

Regards

Steve Fisher
 
D

Dave Peterson

Can you use two helper columns?

Say your data is in column A1:A9999
Put this formula (from Harlan Grove) in B1 and drag down:
=MAX(IF(MID(A1,ROW(INDIRECT("1:1024")),1)="\",ROW(INDIRECT("1:1024"))))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

This will give the position of the last backslash.

Then put this in C1 and drag down.
=MID(A1,B1+1,255)
 
Top