Isolating numeric values from a character string.

[Bad Rabbit]

Thanks to Mr. Excel contributors, I found out how to use MID(A1, SEARCH("ID=",A1),8) to isolate a specific string of data from a URL.
This already made something which seemed impossible actually work which I am grateful for.

However, the we have new URL structures added and now the result of running the formula leaves me with both numeric, alpha and special characters.
What I am now wondering is if there is a formula that I could run on the outcome to only provide me with the numeric values?

 

[XOR LX]

Hi,

Could you provide us with half a dozen or so examples and your desired result(s) in each case?

Regards

 

[Bad Rabbit]

These would be some examples, I am looking for a numeric value which will be between 1 and six characters long, due to the two databases, I am using either SEARCH("ID=",A1),9 or SEARCH("ents/",A1),10.

Actual	Preferred
ID=723#S	723
ID=1009&	1009
ents/1284/u	1284

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[XOR LX]

Thanks. And will the desired extraction always be the only number in the string, as in your examples?

Regards

 

[Bad Rabbit]

Yes, in this case only ever the numbers following ID= or ents/

 

[XOR LX]

Thanks. If you're using Excel 2010 or later:

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&5^19)),1+MMULT(AGGREGATE({14,15},6,1/(ISNUMBER(0+MID(A2,ROW(INDIRECT("1:999")),1)))*ROW(INDIRECT("1:999")),1),{1;-1}))

If not, let me know and I'll give you another solution.

Regards

 

[Bad Rabbit]

I was doing something stupid, your formula works brilliantly!
Thank you so much...

 

[XOR LX]

I assume you realised that formula was set to look at cell A2?

Regards

 

[Peter_SSs]

A couple of other possibilities, noting ...

a) The number is restricted to 1 to 6 digits
b) the number follows "ID=" or "ents/" (I'm hoping following "ts/" is also sufficient?)

.. you could try

=AGGREGATE(14,6,--MID(A2,3+AGGREGATE(15,6,SEARCH({"ID=","ts/"},A2),1),{1,2,3,4,5,6}),1)



If the number can be anywhere (that is, not necessarily following "ID=" etc), but still restricted to a maximum of 6 digits, then try

=AGGREGATE(14,6,--MID(A2,AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1),{1,2,3,4,5,6}),1)


Edit: XOR LX, I like the 5^19 trick. I don't remember ever seeing that before.

 

[Rick Rothstein]

Assuming there is only ever one number to find in a cell, this formula should work no matter what version of Excel is being used...

=LOOKUP(9E+307,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),99),ROW(INDIRECT("1:99"))))