Parsing text to new column

[brianmiller]

Hi,

I am trying to parse text from one column to another. What I want to parse is all text before an underscore and the text after the underscore upto the next underscore e.g.:

1_AveP_6.1_MG5

to become

1_AveP

or

13_Hoo_10.1_H2

to become

13_Hoo

The following gives me all after the first _ and this is where I am
confused on how to adapt it.

=IF(ISNUMBER(SEARCH("_",S2)),RIGHT(S2,LEN(S2)-SEARCH("_",S2)),S2)

Can you help please?

Many thanks

Brian Miller

[jindon]

Hi

if data in cell A1 then

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

[brianmiller]

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

[jindon]

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

[brianmiller]

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