Parsing text to new column

Hi

if data in cell A1 then

=LEFT(A1,FIND("/",SUBSTITUTE(A1,"_","/",2))-1)
hope this helps
jindon

Hi Jinjon,

That worked great! Thanks very much.

To help me understand this I don't suppose you could explain how it does this, i.e. what the formula means at each stage please?

Again, many thanks, you saved me lots of time doing text to columns and then concatenations!

Brian

brianmiller,

=LEFT(A1,FIND("/",SUBSTITUTE(A1,"_","/",2))-1)

Left function extracts number of 2nd argument charctors from the left of 1 st argument.

SUBSTITUTE(A1,"_","/",2)
Substitute function replace 2nd arg:"_" to 3rd arg:"/" of 3rd arg:2 appearance in the text

and Find function will find the position of "/" from the text which 2nd "_" has been substituted by "/".

and the last -1 is to eliminate "/" to be included in the extracted text.

is this enough for you to understand?

rgds,
jindon

Hi,

Thanks, it is a bit more understandable now.

I will probably try it out with some other examples and see if I can adapt it to understand it more.

Regards

Brian