How do I extract a number from an alphanumeric string?

[paulh09]

I'm trying to pull numbers from alphanumeric strings so I can do calculations. For example:

If I had the following strings below, how would I pull out 40, 100, and 150 to a new column and convert these to a number so I can do calculations?

tune-up/pctu40-b
subscription1yr1p/100-b
subcription1yr3p/150-b

I'm using Excel 2007. Thanks for your help.

-P

[Richard Buttrey]

Hi,

One way, as an array formula entered with Ctrl-Shift-Enter

=1*MID(RIGHT(A1,LEN(A1)-FIND("/",A1)),MATCH(1,--ISNUMBER(--MID(RIGHT(A1,LEN(A1)-FIND("/",A1)),ROW($B$1:$B$100),1)),0),SUM(--ISNUMBER(--MID(RIGHT(A1,LEN(A1)-FIND("/",A1)),ROW($B$1:$B$100),1))))

[daddylonglegs]

Assuming the number is always followed by 2 characters at the end of the text, as per your examples, then try this formula in B2 to extract the number (up to 5 digits) from A2

=LOOKUP(10^5,RIGHT(LEFT(RIGHT(A2,7),5),{1,2,3,4,5})+0)

[Richard Buttrey]

Ah, well spotted ddl.

I hadn't seen that common factor.

Regards

[paulh09]

Thanks for the help, but I must be doing something wrong. Would it be possible for me to email one of you a real spreadsheet with real data? Thanks again for the help.

[daddylonglegs]

Can you gives some examples where the suggested formulas don't work? I tested my suggestion on your examples and they all return the correct values I believe - does the formula work for them?

[zbor]

Or this if you always end with -b:

=LOOKUP(99^99,--RIGHT(SUBSTITUTE(A1,"-b",""),ROW($A$1:$A$10)))

[paulh09]

This is what I get when I use your formula.excel_example.png