Extracting a number from a string

[MikeJ100]

Hi all I wish to get the number 1300012207166 from the following E S1 INSTALL 1300012207166 LL57 2EA

I have used the following =IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10),"") thanks to Ablebits.com

and I get 11300012207166600.

I don't understand the formula, however I don't want the last two 00s.

Thanks for your help

 

[Marcílio_Lobão]

MIkeJ100, Good afternoon.

Does this kind of data have a pattern?

 

[Eric W]

This 2-formula procedure finds the largest number in the input string.

	A	B	C
E S1 INSTALL 1300012207166 LL57 2EA	1300012207166

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]15.13[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=TRIM(MID(A2,ROUND(MOD(B2,1)*100,0),B2))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=MAX(IF(ISNUMBER(MID(A2&"A",ROW(INDIRECT("1:"&LEN(A2))),TRANSPOSE(ROW(INDIRECT("1:"&LEN(A2)))))+0),TRANSPOSE(ROW(INDIRECT("1:"&LEN(A2))))+ROW(INDIRECT("1:"&LEN(A2)))/100))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



It returns the result as text so that Excel won't convert it to exponential notation. It's still possible to return false results depending on the data. If you know that the number that you're looking for must have a certain length, say 10-15 digits, you can shorten it as follows:

=MAX(IF(ISNUMBER(MID(A2&"A",ROW(INDIRECT("1:"&LEN(A2))),{10,11,12,13,14,15})+0),{10,11,12,13,14,15}+ROW(INDIRECT("1:"&LEN(A2)))/100))

with Control+Shift+Enter.

 

[MikeJ100]

Eric, Wow I don't understand the mechanics, but it works.

Thank you

 

[hiker95]

MikeJ100,

If cell A2 contains E S1 INSTALL 1300012207166 LL57 2EA

In cell C2 enter the following formula =TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),300,100))