File names from DIR /S Output

[Sriram N A \(MICO/PJ-SAP-PP\) *]

I have a text file created with the DIR /S /B DOS command from a folder tree
on our server.

The output is the list of file names with the full path.

I need to get only the file names (the portion after the last "\") into a
separate Excel column for further manipulation.

This appears to be inordinately difficult using Excel functions, since the
position of the "\" is variable in the text representing the path and file
name.

Is there any way at all of constructing a formula which will list the
filenames within the given strings? I don't want to use a macro.

Sriram

 

[Bob Phillips]

Here is a formula that will do it

=MID(A1,FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+
1,99)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Sriram N A \(MICO/PJ-SAP-PP\) *]

Now that's creative thinking! Thanks muchly.

Sriram

Here is a formula that will do it

=MID(A1,FIND("~",SUBSTITUTE(A1,"\","~",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+
1,99)