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.

 

[Rick Rothstein]

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]

We do not know the full extent of how the OP's text strings are constructed, but if the following are possible, then your formula will report the wrong answer...

ABC 12D34 EFG 12345 HIJ

ABC 12E34 EFG 12345 HIJ

ABC 12DEC EFG 12345 HIJ

 

[Haluk]

We do not know the full extent of how the OP's text strings are constructed,.........

Then, maybe the following can be used if the data structure is unknown;

(It works for 1 block of numeric data placed within upto 8 blocks of string data within a cell)

Sample file:

https://drive.google.com/open?id=1oU-4LJ0mznZo4NckRQBACZki3R1rLvvO

 

[markmzz]

[LEFT][I][COLOR=#574123][FONT=Tahoma][B]Markmzz [/B]- your suggestion results in a zero, so not quite sure what's happened there.[/FONT][/COLOR]
[/I]
[COLOR=#574123][FONT=Tahoma][I]For info this is the value in the cell I've tried these suggestions in;[/I][/FONT][/COLOR]

[B][COLOR=#574123][FONT=Tahoma][I]G.T. 27415 FGTYITFSDDI (Z2)(UHSG)[/I][/FONT][/COLOR][/B][/LEFT]

Hi Sharky12345!

The table below shows many examples of texts with a single number in every one of them.

Put the formula below in B2 and copy down (Array Formula - use Ctrl+Shift+Enter and not just Enter to enter the formula):

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

Look at this:

	A	B	C
1	Value	Number
2	SBD 12345 DBE1	12345
3	2345 ABS23 23EFRG33	2345
4	ABSD23 ABRF33 32456	32456
5	SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 782456	782456
6	SBD 12345E DBE112345 23 23EFRG33ABSD23 ABRF33 E32456	23
7	SBD 12345E 112345 ABS23 23EFRG33ABSD23 ABRF33 32456D	112345
8	SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 54321 ABRF3332456	54321
9	SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 32456SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 32456SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 32456SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 54321 ABRF3332456	54321
***	*******************************************	*********	****

<tbody>
</tbody>

I hope this helps.

Markmzz

 

[markmzz]

Hi Sharky12345!

I forgot your example (G.T. 27415 FGTYITFSDDI (Z2)(UHSG)) in my last post. Look at the row 10 of the table below:

	A	B	C
1	Value	Number
2	SBD 12345 DBE1	12345
3	2345 ABS23 23EFRG33	2345
4	ABSD23 ABRF33 32456	32456
5	SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 782456	782456
6	SBD 12345E DBE112345 23 23EFRG33ABSD23 ABRF33 E32456	23
7	SBD 12345E 112345 ABS23 23EFRG33ABSD23 ABRF33 32456D	112345
8	SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 54321 ABRF3332456	54321
9	SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 32456SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 32456SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 32456SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 54321 ABRF3332456	54321
10	G.T. 27415 FGTYITFSDDI (Z2)(UHSG)	27415
***	*******************************************	*********	****

<tbody>
</tbody>

I hope this helps.

Markmzz

 

[markmzz]

Another way. Try the Normal Formula (use just Enter to enter the formula) below too:

=IFERROR(LOOKUP(9^9,--MID(SUBSTITUTE(A2," ",REPT(" ",99)),1+99*(ROW(INDIRECT("1:50"))-1),99)),"")

Markmzz

 

[Peter_SSs]

@markmzz
I think all your suggestions would fail for a string like "SBD 12345 DBE1 231E2 ABFR34".
@Haluk
I think your latest solution also fails for the above example.
@sharky12345
If you might consider a user-defined function, then you could try this one.

Function ExtractNum(s As String) As Long
  Static RX As Object
  
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\b\d+\b"
  ExtractNum = RX.Execute(s)(0)
End Function

Book1
	A	B
1	Value	Number
2	SBD 12345 DBE1 231E2 ABFR34	12345
3	2345 ABS23 23EFRG33	2345
4	ABSD23 ABRF33 32456	32456
5	SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 782456	782456
6	SBD 12345E DBE112345 23 23EFRG33ABSD23 ABRF33 E32456	23
7	SBD 12345E 112345 ABS23 23EFRG33ABSD23 ABRF33 32456D	112345
8	SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 54321 ABRF3332456	54321
9	SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 32456SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 32456SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 32456SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 54321 ABRF3332456	54321
Extract Number

Cell Formulas
Range	Formula
B2	=ExtractNum(A2)

 

[sharky12345]

Guys - I'm very grateful for all of the work you've put into this!

I'll give all of your suggestions a try.

Thanks to all!

 

[Haluk]

@Haluk
I think your latest solution also fails for the above example.

<tbody> </tbody>

<tbody>
</tbody>

I have already mentioned in my post # 12 that;

(It works for 1 block of numeric data placed within upto 8 blocks of string data within a cell)

In your example, your data is housing 2 numeric values 12345 and 231E2.

 

[markmzz]

@markmzz
I think all your suggestions would fail for a string like "SBD 12345 DBE1 231E2 ABFR34".

Hi Peter!

All is ok with you?

You're right. Maybe the Normal Formula below Works with all your's suggestion?

=IFERROR(LOOKUP(9^9,--MID(SUBSTITUTE(SUBSTITUTE(A13,"E","A")," ",REPT(" ",99)),1+99*(ROW(INDIRECT("1:50"))-1),99)),"")

What you think?

Markmzz

 

[Haluk]

According to Peter_SSs's warning in his message #16 , the revised sample file can be downloaded from Google Drive link below,

https://drive.google.com/open?id=1d-uiKOAGRW8Gp4rrr_5HOd4IL7uO-5H5

The screenshot of the page is;