delete all text to the left of...

  • Thread starter Thread starter Sheila
  • Start date Start date
S

Sheila

I have cells with just text (filenames and paths), 9000 of them in
fact. I want to delete the path and just have the name of the file so
I assume I need to delete all text to the left of the rightmost "\".
How do I do this to get a file list in column B? (Filling down I
assume)I

Any help appreciated and TIA

Sheila
 
Hi Sheila!

Try the helper formula:

=MID(SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,255)

I built in three stages:
First stage:
F1:
=SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))
Substitutes a * for the last \

Second Stage:
G1:
=MID(F1,FIND("*",F1)+1,255)
Returns the file name only

Third Stage
In G1 copy the formula F1 as a substitute for the references to F1
 
To do this, you'll need an array formula.

Assume cell A1 contains the following c:\My Documents\Budgets\Jan04.xls
In cell B1, enter =MID(B1,MAX((MID(B1,ROW(INDIRECT("1:" &
LEN(B1))),1)="\")*ROW(INDIRECT("1:" &LEN(B1))))+1,LEN(B1))

DO NOT hit the enter key, instead hit Ctrl-Shift-Enter and this will create
an array formula with braces around it. Should give you what you are
looking for.

You could use fill or copy to copy this down 9000 rows. However, I would
use keyboard shortcuts. Assuming there are no blank spaces in column A,
select any cell then press End and then Down Arrow. This should take you to
the bottom. Now select the bottom most cell in column B and enter some
characters. Any characters will do. You are just marketing a stopping
point. Assuming no data in column B except for this cell and the formula in
B1, select End and then Up Arrow and you should fly to the top. Now, while
on the formula cell, select copy (ctrl-C or click the icon). Next, hold
down Shift then select End and Down Arrow. This should "paint" the range
all the way to the dummy value at the bottom. Take your finger off of
Shift, hit Enter and presto, you've copied the formulas. I can do this set
of key strokes in probably three or for seconds so once you know it, it is
very quick.

FYI - I wrote a neat little Excel program that copies the names and paths of
all of the files in a certain folder. Sure beats typing them in. I plan to
post in on my web-site. If you are interested in this freebie, reply to the
group and I'll do it sooner rather than later.


- John
www.JohnMichl.com
 
I have cells with just text (filenames and paths), 9000 of them in
fact. I want to delete the path and just have the name of the file so
I assume I need to delete all text to the left of the rightmost "\".
How do I do this to get a file list in column B? (Filling down I
assume)I
Click to expand...

Presumably you have your pathnames in column A. If so, copy column A into column
B, select the column B range, and run the menu command Edit > Replace, replacing

*\

with nothing. So *\ in the 'Find what' field in the Replace dialog, and
nothing at all in the 'Replace with' field.
 
Try the helper formula:

=MID(SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),
FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,255)
Click to expand...
...

Could be done more compactly with the array formula

=MID(A1,MAX(IF(MID(A1,ROW(INDIRECT("1:256")),1)="\",
ROW(INDIRECT("1:256"))))+1,256)
 
and all that does is leave the list of file names without the "\", but
the full path intact. Thanks anyway, but it is now sorted.

Sheila
 
Sheila said:
and all that does is leave the list of file names without the "\", but
the full path intact. Thanks anyway, but it is now sorted.
Click to expand...
....

It may not include the rightmost \ with the filename, but it does strip off
the full path. If you're working with a copy of col A data in col B, then
Edit > Replace on col B will leave col A unchanged. If you want the
rightmost \ as well as the base filename, that's harder.
 
it does work too, your so right, i didnt read it correctly, thanks so
much for that, nice little trick to remember.

sheila
 
Back
Top