data find

Moogie

New Member
Joined
Sep 16, 2002
Messages
47
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try this out. It's a monster, so don't even think about asking how it works. I already forgot.

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

Moogie

New Member
Joined
Sep 16, 2002
Messages
47
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

Well-known Member
Joined
Dec 22, 2006
Messages
512
Here is my solution!
Code:
=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

New Member
Joined
Sep 16, 2002
Messages
47
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
basic syntax would be

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

so
Code:
=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),"")
 

Forum statistics

Threads
1,181,648
Messages
5,931,212
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top