Separate numbers mingled with characters and text

**hawk93** · 02-01-2012, 02:57 PM

I have a long column containing numbers mingled within characters and text. I want to use and equation to extract the numbers. Examples of cell contents are as follows:

TRIM 2-OUT
2-OUT TRIM
TRIM 4-OUT
TRIM 4-OUT
4-OUT TRIM

Cell content is in column “I”. Want the result to appear in column “J”.
In the first line of the example I need the 2 to appear in column “J”
In the fifth line of the example I need the 4 to appear in column “J”
Number to extract will never exceed 10

Can this be accomplished with an equation or will this require a macro?
Can you provide requested equation or macro?

**DBY** · 02-01-2012, 03:58 PM

Hello
Try the following Array formula, which extracts numbers from an alpha-numeric text string.

=1*MID(I1,MATCH(TRUE,ISNUMBER(1*MID(I1,ROW($1:$20),1)),0),COUNT(1*MID(I1,ROW($1:$20),1)))

This is entered with Crtl+Shift+Enter. You can then drag it down column J.

This will work with text strings up to 20 characters you'll have to adjust the row() part of the formula if they're any longer.

Hope this helps.

**hawk93** · 02-01-2012, 04:33 PM

Equation returns an "#N/A". The column being read is formated TEXT. The column containing the equation provided is formatted GENERAL.
This is the equation as entered: =1*MID(I70,MATCH(TRUE,ISNUMBER(1*MID(I70,ROW($1:$20),1)),0),COUNT(1*MID(I70,ROW($1:$20),1)))
I do not see any errors.

**DBY** · 02-01-2012, 04:47 PM

Hello
Have you entered the formula with Ctrl+Shift+Enter? Otherwise it will return an #N/A. You should see {} around the formula if entered correctly.

**hawk93** · 02-01-2012, 05:31 PM

I did see the {}

I’ve attached a file.

As example I need the alphanumeric in cell I70 to be read and result of 2 be displayed in S70.

The same would apply to I71 with the result of 4 displayed in cell S71.

**DBY** · 02-01-2012, 06:11 PM

Hello
I've put the formulas in to your sheet in the yellow cells in column S, referencing the cells in column I. It seems to be working fine returning 2,4,4 respectively.