Extract whole number from string

sharky12345

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,422
Office Version
  1. 2016
Platform
  1. Windows
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)))
Click to expand...

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
Click to expand...

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.
 
Sort by date Sort by votes
Haluk said:
You can add + 0 to the end of the formula, if you want to get the result as a number.

Code: 
=TRIM(MID(SUBSTITUTE(A11," ",REPT(" ",100)),100,100)) [COLOR=#ff0000][B]+0[/B][/COLOR]
Click to expand...
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
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Last edited:
Upvote 0
Code: 
[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:

[TABLE="class: grid, width: 449"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Value[/TD]
[TD]Number[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SBD 12345 DBE1[/TD]
[TD="align: right"]12345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2345 ABS23 23EFRG33[/TD]
[TD="align: right"]2345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABSD23 ABRF33 32456[/TD]
[TD="align: right"]32456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 782456[/TD]
[TD="align: right"]782456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]SBD 12345E DBE112345 23 23EFRG33ABSD23 ABRF33 E32456[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]SBD 12345E 112345 ABS23 23EFRG33ABSD23 ABRF33 32456D[/TD]
[TD="align: right"]112345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 54321 ABRF3332456[/TD]
[TD="align: right"]54321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]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[/TD]
[TD="align: right"]54321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*******************************************[/TD]
[TD]*********[/TD]
[TD]****[/TD]
[/TR]
</tbody>[/TABLE]


I hope this helps.

Markmzz
 
Upvote 0
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:


[TABLE="class: grid, width: 449"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Value[/TD]
[TD]Number[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SBD 12345 DBE1[/TD]
[TD="align: right"]12345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2345 ABS23 23EFRG33[/TD]
[TD="align: right"]2345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABSD23 ABRF33 32456[/TD]
[TD="align: right"]32456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 782456[/TD]
[TD="align: right"]782456[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]SBD 12345E DBE112345 23 23EFRG33ABSD23 ABRF33 E32456[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]SBD 12345E 112345 ABS23 23EFRG33ABSD23 ABRF33 32456D[/TD]
[TD="align: right"]112345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 54321 ABRF3332456[/TD]
[TD="align: right"]54321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]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[/TD]
[TD="align: right"]54321[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]G.T. 27415 FGTYITFSDDI (Z2)(UHSG)[/TD]
[TD="align: right"]27415[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*******************************************[/TD]
[TD]*********[/TD]
[TD]****[/TD]
[/TR]
</tbody>[/TABLE]


I hope this helps.

Markmzz
 
Upvote 0
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
 
Last edited:
Upvote 0
@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.
Code: 
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
AB
1ValueNumber
2SBD 12345 DBE1 231E2 ABFR3412345
32345 ABS23 23EFRG332345
4ABSD23 ABRF33 3245632456
5SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 782456782456
6SBD 12345E DBE112345 23 23EFRG33ABSD23 ABRF33 E3245623
7SBD 12345E 112345 ABS23 23EFRG33ABSD23 ABRF33 32456D112345
8SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 54321 ABRF333245654321
9SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 32456SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 32456SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 ABRF33 32456SBD 12345E DBE112345 ABS23 23EFRG33ABSD23 54321 ABRF333245654321
Extract Number
Cell Formulas
RangeFormula
B2=ExtractNum(A2)
 
Upvote 0
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!
 
Upvote 0
Peter_SSs said:
@Haluk
I think your latest solution also fails for the above example.[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

I have already mentioned in my post # 12 that;

Haluk said:
(It works for 1 block of numeric data placed within upto 8 blocks of string data within a cell)
Click to expand...

In your example, your data is housing 2 numeric values 12345 and 231E2.
 
Upvote 0
Peter_SSs said:
@markmzz
I think all your suggestions would fail for a string like "SBD 12345 DBE1 231E2 ABFR34".
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Extract 8 character number from text string
2
Replies
15
Views
964
Fluff
Fluff
C
  • Solved
Extract text between second and first space from the right in text string
Replies
9
Views
3K
Cubist
Cubist
A
  • Solved
Extracting Minutes From Coordinates (D-M-S)
Replies
7
Views
142
Peter_SSs
Peter_SSs
V
  • Solved
Extracting numbers from a string and adding them up
2
Replies
19
Views
1K
Vector8086
V
2BLUE4U
  • Solved
Extracting Numbers from Text String
2
Replies
10
Views
2K
2BLUE4U
2BLUE4U

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top