Extract whole number from string

[sharky12345]

I'm having issues trying to extract a number from a string.

I've currently got this array formula;

=IF(A11="","",(SUMPRODUCT(MID(0&A11,LARGE(INDEX(ISNUMBER(--MID(A11,ROW($1:$44),1))*ROW($1:$44),0),ROW($1:$44))+1,1)*10^ROW($1:$44)/10)))

The issue is that there are various numbers at various points within the string and what I need to do is extract only the number which will always be on it's own, like this;

GD 12345 GTH1

In this case I'd want to extract 12345 and nothing else, so ignoring any number which is attached to a letter. There will never be more than 1 'string of numbers' with nothing else, but the number of characters either before or after the number I want will vary, so I need to take that into account please.

 

[Dryver14]

sharky, not to detract from formulas but have you tried a separate column to the information with flashfill on the data tab? Excel 2010> I think

when it works it really is very good for things like this

 

[Haluk]

@sharky12345;

If the data is in cell A11, you can try this;

=TRIM(MID(SUBSTITUTE(A11," ",REPT(" ",100)),100,100))

 

[markmzz]

I'm having issues trying to extract a number from a string.

I've currently got this array formula;

The issue is that there are various numbers at various points within the string and what I need to do is extract only the number which will always be on it's own, like this;

In this case I'd want to extract 12345 and nothing else, so ignoring any number which is attached to a letter. There will never be more than 1 'string of numbers' with nothing else, but the number of characters either before or after the number I want will vary, so I need to take that into account please.

Maybe the Array Formula (use Ctrl+Shift+Enter to enter the formula) below can help you:

=IF(A1="","",SUM(IFERROR(--MID(SUBSTITUTE(A1," ",REPT(" ",99)),1+99*(ROW(INDIRECT("1:50"))-1),99),0)))

Markmzz

 

[Rick Rothstein]

I'm having issues trying to extract a number from a string.

The issue is that there are various numbers at various points within the string and what I need to do is extract only the number which will always be on it's own, like this;

GD 12345 GTH1

Is your number always located between the second and third blank space?

If not, can the number ever be located as the beginning or end of the text?

 

[sharky12345]

Thanks for the replies.....

Rick - it can be anywhere I'm afraid....

 

[sharky12345]

Haluk - your suggestion results in text from the cell, so not what I was expecting.

Markmzz - your suggestion results in a zero, so not quite sure what's happened there.

For info this is the value in the cell I've tried these suggestions in;

G.T. 27415 FGTYITFSDDI (Z2)(UHSG)

 

[markmzz]

Markmzz - your suggestion results in a zero, so not quite sure what's happened there.

For info this is the value in the cell I've tried these suggestions in;

Here all is Ok. Did you use Ctrl+Shift+Enter to enter the formula?

Markmzz

 

[Haluk]

For info this is the value in the cell I've tried these suggestions in;

G.T. 27415 FGTYITFSDDI (Z2)(UHSG)

The formula i suggested extracts 27415 from the string. Isn't it the value you are looking for?

 

[Haluk]

You can add + 0 to the end of the formula, if you want to get the result as a number.

=TRIM(MID(SUBSTITUTE(A11," ",REPT(" ",100)),100,100)) [COLOR=#ff0000][B]+0[/B][/COLOR]