Extracting multiple numbers in alphanumeric string

Hi All,

I need help extracting multiple numbers in an alphanumeric string.

Example:

A) Gift Set -- 3.4 oz Eau De Toilette Spray + 3 oz After Shave Balm + 2.75 oz Deodorant Stick + .25 oz Mini EDT Spray
B) Gift Set -- 4 oz Eau De Cologne Spray + 1 oz Eau De Cologne Spray
C) Gift Set -- 1.7 oz Eau De Toilette Spray + 1.7 oz Body Lotion + 1.7 oz Shower Gel
D) Gift Set -- 3.4 oz Eau De Toilette Spray + 4.4 oz Shower Gel
E) Gift Set -- 3.4 oz Cologne Spray + 3.3 oz Hair & Body Wash + 2.5 oz Deodorant Stick + 3 oz Soap

Prefered results:

A) 3.4, 3, 2.75, .25
B) 4,1
C) 1.7, 1.7, 1.7
D) 3.4,4.4
E) 3.4, 3.3, 2.5, 3

I want to extract the numbers so I could add them. If there's a way to bypass the extraction, it'd be better.

I'd appreciate any help. Thanks!

Hi,

try this ARRAY formula into B1 for A:

=SUM(MID(A1;LARGE(ISNUMBER(--MID(A1;ROW($1:$140);1))*
ROW($1:$140);ROW($A$1:INDEX($A:$A;COUNT(--MID(A1;ROW($1:$140);1)))));1)*
10^(ROW(INDEX($1:$140;COUNT(--MID(A1;ROW(INDEX($1:$140;1;1):
INDEX($1:$140;LEN(A1);1));1));1):INDEX($1:$140;1;1))-1))

it will add all the numbers together, however I'm not sure how to proceed further, perhaps others will :-)

didn't work. thanks though

Originally Posted by Amik555

Hi,

try this ARRAY formula into B1 for A:

=SUM(MID(A1;LARGE(ISNUMBER(--MID(A1;ROW($1:$140);1))*
ROW($1:$140);ROW($A$1:INDEX($A:$A;COUNT(--MID(A1;ROW($1:$140);1)))));1)*
10^(ROW(INDEX($1:$140;COUNT(--MID(A1;ROW(INDEX($1:$140;1;1):
INDEX($1:$140;LEN(A1);1));1));1):INDEX($1:$140;1;1))-1))

it will add all the numbers together, however I'm not sure how to proceed further, perhaps others will :-)

2nd-4th number seem okay but i have a problem with the first number... instead of 3.4, it shows 4.

Originally Posted by Glenn Kennedy

Minor adjustment to the above attachment -removing redundant & potentially confusing cells.

This is the solution. I had to double check for some word inclusion, minimal errors nonetheless. Great job! Thanks a lot!

Originally Posted by newdoverman

Here is my horrendous formula that returns all the elements into 1 cell separated by commas.

Formula:

Please Login or Register  to view this content.

This is the result: