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]

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)

Peter: a couple of points:

1) If the number begins with one or more zeroes, you would need to wrap these in a TEXT formula.
2) There is the (albeit small) chance that certain characters will be present in the string which will cause this to give incorrect results, as is always the danger when using this approach. For example (and given the appropriate language/regional date settings:

ID=10JUN#S

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

Thanks. I sometimes prefer 7^18!

 

[XOR LX]

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"))))

You've done the same! See my last post to Peter.

It's surprising how often this is overlooked in mixed text/number strings, i.e. the possibility of strings which can be interpreted as e.g. dates or scientific notation.

(If ID=10JUN#S doesn't produce 41800 with your settings, perhaps ID=JUN10#S does.)

Regards

 

[mfexcel]

Try this.
=MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&{0;1;2;3;4;5;6;7;8;9})),COUNT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0))

CTRL SHIFT ENTER

Learn this trick from Rick at the following thread.
http://www.mrexcel.com/forum/excel-questions/777395-extract-text-number-cell.html

 

[XOR LX]

Try this.
=MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&{0;1;2;3;4;5;6;7;8;9})),COUNT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0))

CTRL SHIFT ENTER

Learn this trick from Rick at the following thread.
http://www.mrexcel.com/forum/excel-questions/777395-extract-text-number-cell.html

That's good! And rigorous!

Regards

 

[mfexcel]

Thanks so much!

It took me minutes to figure out what the 7^18 and 5^19 do. Interesting trick!

That's good! And rigorous!

Regards

 

[XOR LX]

Thanks so much!

It took me minutes to figure out what the 7^18 and 5^19 do. Interesting trick!

I should've mentioned that I always used to use 7^18 until shg mentioned 5^19 as a (smaller) alternative!

Cheers

 

[Rick Rothstein]

You've done the same! See my last post to Peter.
It's surprising how often this is overlooked in mixed text/number strings, i.e. the possibility of strings which can be interpreted as e.g. dates or scientific notation.
(If ID=10JUN#S doesn't produce 41800 with your settings, perhaps ID=JUN10#S does.)

D-a-m-n, the ever helpful Excel at work!

 

[Rick Rothstein]

I should've mentioned that I always used to use 7^18 until shg mentioned 5^19 as a (smaller) alternative!

5^19 is the shortest of the form X^Y at 14 digits.

It is a shame that it comes in at 20 digits because this would surely be the easiest to remember... 3^45

 

[XOR LX]

5^19 is the shortest of the form X^Y at 14 digits.

It is a shame that it comes in at 20 digits because this would surely be the easiest to remember... 3^45

Interesting. But we can still use 3^45 since its abridged, scientific notation is also pandigital!!

Regards

 

[Rick Rothstein]

Interesting. But we can still use 3^45 since its abridged, scientific notation is also pandigital!!

Oh, you are right... when concatenated, 3^45 tacks on 2.95431270655083E+21 and all the necessary digits are available to be found.