Extraction of portions of a cell

[TomPose]

I have the need to extract sub portions of a cell. The catch is that I need to get the portion according to what is in the cell.

For example:

If I have a path and file name in the cell
\Directory1\directory2\anotherdirectory\filename.ext
how do I get just the name of anotherdirectory, or filename.ext with out getting all of the stuff around it?

each part is of different length... thats where I get stuck. Does it need to be a vb snippet, or a macro??

help!!

Tom

 

[Zathras]

Experiment with this:
[blue]

A1: '\Directory1\directory2\anotherdirectory\filename.ext
A2: =IF(ISERROR(FIND("\",A1)),A1,MID(A1,FIND("\",A1)+1,999))

[/color]
Copy A2 down into A3:A7 and watch what happens.

 

[DarrenPower]

Hi chaps,

Is there any chance of someone explaining this formula piece by piece? I've nicked it but need to amend it and unfortunately, it's all greek to me...

Cheers,

Darren.

 

[Zathras]

I am going to assume you have looked in the help file and you have a general understanding of the syntax and use of the individual functions, especially the IF function. If that assumtion is wrong, stop reading now and go to the Excel help file, then come back.

=IF(ISERROR(FIND("\",A1)),A1,MID(A1,FIND("\",A1)+1,999))

At the innermost level, the FIND function says this:

Search the text in cell A1 from the first (left-hand) character to see if there is a back-slash character somewhere in the text. The FIND function returns either the location of the backslash character or an error condition indicated by #VALUE!

At the middle level, the ISERROR function says this:

If the FIND function failed to find the "\" character (and therefore returned an error, return the value True. If a backslash character was found, then there was no error so return False. (A little backwards, I admit.)

At the outermost level, the IF function says this:

If the ISERROR function returned true (indicating no backslash found) then return the value from A1, otherwise return the result of the MID function.

The MID function says get that portion of the text in A1 that starts in the character following the backslash (+1) and continues for 999 characters or the end of the text whichever comes first. (A purist might use another function and calculation to get the exact number of characters to take, but this is simpler.)