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?

 

[Aladin Akyurek]

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.

Just curious: For which values did you get text? And, Care to post some examples from that 5% range?

 

[Dave Patton]

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

The formula that I suggested worked fine on the sample data and it gave a number result.

 

[floored]

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

The first example I gave in my initial post resulted in a return of "treats".

As to samples of the exceptions now....

Cold Remedies 532; Cough Remedies 534
Tea, Packaged & Bags , 315
Frozen Dinner Entrée , Full Cal , 483
Refrigerated Entrée, Meatless , 477


All errors which would be anticipated given the formula.

 

[Dave Patton]

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

This group of data is not consistent with your initial information.

 

[Aladin Akyurek]

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

The first example I gave in my initial post resulted in a return of "treats".

As to samples of the exceptions now....

Cold Remedies 532; Cough Remedies 534
Tea, Packaged & Bags , 315
Frozen Dinner Entrée , Full Cal , 483
Refrigerated Entrée, Meatless , 477


All errors which would be anticipated given the formula.

The shorter formula Dave suggested inexpensive. If you are willing to apply...

=IF(ISNUMBER(SETV(--WMID(A1,WORDCOUNT(A1,","),1,","),1)),GETV(1),IF(ISNUMBER(SETV(--WMID(A1,WORDCOUNT(A1),1),2)),GETV(2),#N/A))

you could catch some of the more difficult cases. Note that the entries like Cold Remedies 532; Cough Remedies 534 will result in the last number the string contains.

 

[floored]

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

This group of data is not consistent with your initial information.

Exactly why it has returned the errors with the revised formula. -- I'm working with other people's sloppy data entry

 

[UX3ME4U]

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

hope this work for you :

=VALUE(RIGHT(B2,5))

 

[UX3ME4U]

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

hope this work for you!!

=VALUE(RIGHT(B2,5))