Extracting number from a string of text in a particular cell

[floored]

I am working on cleaning up some data on an existing excel spreadsheet. In one column, there is a descriptive string of text which is followed by a "," and then a number identifying its category -- there are cases where there are multiple sets of data like this in one cell. I want to extract the first number.

Sample data

1)Dog Snacks/Treats , 244
2)Film/Film Processing, 132
3)Paper Towels, 222
4)Paper tablecloths , 221,Placemats ,223
5)Wine, 31

Desired Results

1) 244
2) 132
3) 222
4) 221
5) 31

I have attempted to nest the find formula (looking for the ,) inside Right and Left formulas, but for some reason I can't get it to select the numbers. For example when I use the formula right(A1,find(",",A1)) it returns "Snacks/Treats , 244".

I've considered exporting into a text file which I could open as a csv file to seperate these strings, but given the volumne of data I need to do this on, I'm concerned about maintaing the data's integrity.

Any thoughts on a formula which would extract this data?

 

[Joe4]

Re: Extracting number from a string of text in a particular

You could highlight the column and perform a "Text to Columns" (found under the Data drop down menu), selecting comma as your delimiter. This will break your data up into multiple columns. The numbers you are looking for should be in the second column. You can delete the other columns, if desired.

 

[Brian from Maui]

Re: Extracting number from a string of text in a particular

I am working on cleaning up some data on an existing excel spreadsheet. In one column, there is a descriptive string of text which is followed by a "," and then a number identifying its category -- there are cases where there are multiple sets of data like this in one cell. I want to extract the first number.

Sample data

1)Dog Snacks/Treats , 244
2)Film/Film Processing, 132
3)Paper Towels, 222
4)Paper tablecloths , 221,Placemats ,223
5)Wine, 31

Desired Results

1) 244
2) 132
3) 222
4) 221
5) 31

I have attempted to nest the find formula (looking for the ,) inside Right and Left formulas, but for some reason I can't get it to select the numbers. For example when I use the formula right(A1,find(",",A1)) it returns "Snacks/Treats , 244".

I've considered exporting into a text file which I could open as a csv file to seperate these strings, but given the volumne of data I need to do this on, I'm concerned about maintaing the data's integrity.

Any thoughts on a formula which would extract this data?

Text to Columns, with the "," as the delimiter would work for everything except the paper tablecloths

 

[Dave Patton]

Re: Extracting number from a string of text in a particular

with a function from the free add-in Morefunc (see list of add-ins at top of message list)

=--WMID(A2,2,1,",")

 

[Joe4]

Re: Extracting number from a string of text in a particular

Brian,

Why won't the Text to Columns work for the Paper Tableclothes? It returns the first number in the second column, just like they requested (I tested it myself just to make sure). It returns two more extra columns, but they can just ignore or delete those. The second column will contain all the data they need.

 

[Brian from Maui]

Re: Extracting number from a string of text in a particular

Brian,

Why won't the Text to Columns work for the Paper Tableclothes? It returns the first number in the second column, just like they requested (I tested it myself just to make sure). It returns two more extra columns, but they can just ignore or delete those. The second column will contain all the data they need.

You're right, after re-reading the OP's post , placemats was not a requirement.

 

[floored]

Re: Extracting number from a string of text in a particular

Brian & J.,

thanks for your thoughts. It seems like a good idea to go from text to column except that it overwrites all the data to the right. I have too many columns to merely insert columns to get around this. Excel is not happy when I shift cells off the page

Dave,

As soon as I grab the add-in I'll try your solution.

Thanks!

floored

 

[Brian from Maui]

Re: Extracting number from a string of text in a particular

Brian & J.,

thanks for your thoughts. It seems like a good idea to go from text to column except that it overwrites all the data to the right. I have too many columns to merely insert columns to get around this. Excel is not happy when I shift cells off the page

Dave,

As soon as I grab the add-in I'll try your solution.

Thanks!

floored

Actually, you can specify where you want the list copied to in step 3 of the wizard.

 

[IML]

Re: Extracting number from a string of text in a particular

Another formula based option:
=MID(A1,FIND(",",A1)+1,FIND("^",SUBSTITUTE(A1,",","^",2)&"^")-FIND(",",A1)-1)+0


which assumes
-everything in your list will have one comma
-The ^ is not an expected thing to find in your list.

 

[floored]

Re: Extracting number from a string of text in a particular

Dave,

The formula you provided was returning text rather than the numbers, but when I combined it with my original formula, it's now working about 95% of the time. Final solution that worked was:

=--WMID(right(a1,find(",",a1)),2,1,",")

Thanks everyone for your help.