Bringing back info from a field

  • Thread starter Thread starter scfisher_list
  • Start date Start date
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
 
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)
 
Back
Top