data find

[Moogie]

I have the following single string of data in an Excel cell. In the next cell I am trying to extract the data after the second comma, (1). I tried to use find but cannot identify I need the info after the second comma. Can anyone help please?

CLP,1009455M107-008,1,20550,7995.6,,MC,34128973

Thanks in advance

 

[Diffy]

Will it always be 1 character long?

 

[Jonmo1]

Try this out. It's a monster, so don't even think about asking how it works. I already forgot.

=LEFT(REPLACE(SUBSTITUTE(A1,",","^",2),1,FIND("^",SUBSTITUTE(A1,",","^",2),1),""),FIND(",",REPLACE(SUBSTITUTE(A1,",","^",2),1,FIND("^",SUBSTITUTE(A1,",","^",2),1),""))-1)

 

[Moogie]

Answer

I believe it will only be one, but just in case, is there a way of pulling whatever is between the second and third comma positions?

Thanks!

 

[Diffy]

Here is my solution!

=LEFT(RIGHT(RIGHT(A1,LEN(A1)-FIND(",",A1,1)),LEN(RIGHT(A1,LEN(A1)-FIND(",",A1,1)))-FIND(",",RIGHT(A1,LEN(A1)-FIND(",",A1,1)),1)),FIND(",",RIGHT(RIGHT(A1,LEN(A1)-FIND(",",A1,1)),LEN(RIGHT(A1,LEN(A1)-FIND(",",A1,1)))-FIND(",",RIGHT(A1,LEN(A1)-FIND(",",A1,1)),1)),1)-1)

Try it, I bet it works!

 

[Moogie]

Only clp rows

Wow, it does work! Since I will have multiple rows with various information, is there add to this formula so it will work on rows where the cell data only begins with CLP? An IF statement?

Thanks

 

[Jonmo1]

basic syntax would be

=IF(LEFT(A1,3)="CLP",monsterformulahere,"")

so

=IF(LEFT(A1,3)="CLP",LEFT(REPLACE(SUBSTITUTE(A1,",","^",2),1,FIND("^",SUBSTITUTE(A1,",","^",2),1),""),FIND(",",REPLACE(SUBSTITUTE(A1,",","^",2),1,FIND("^",SUBSTITUTE(A1,",","^",2),1),""))-1),"")

 

[Moogie]

Thank you both Jonmo1 and Diffy. This works great!