Extract fixed number from variable text strings

[IM22]

Hi,

Does anybody know if there's a way to extract a number from a text string containing it, for example:

randomtextstringhere12345678anotherrandomtextstringhere
randomtextstringhereagain24682468anotherrandomtextstring
randomtext13579135anotherrandomtextstringhere

So the number is a fixed length but can appear anywhere in the text and the text is variable in length.

[Cullen8]

There are many ways to do this, here's one. where A1 is your data..

=LOOKUP(9.99E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))

Hope this helps,
Cullen

[Marcol]

Or without using INDIRECT()

Please Login or Register  to view this content.

[icestationzbra]

will you ever have a situation where you have string like this:

randomtextstringhere12345678anotherrandomtextstringhere000999

i.e., numbers appearing at various places in the string?

[IM22]

Hi Cullen,

That is great, thanks for your help. The only slight problem with it, that affects some of the data, is that sometimes there are other numbers in the text string before the number I'm looking for. Is there any way that I can target only say a 12 digit number in the text?

Sorry about not putting this in the original question but I didn't realise it would be a problem until I ran your solution.

Thanks,
Ian

[IM22]

will you ever have a situation where you have string like this:

randomtextstringhere12345678anotherrandomtextstringhere000999

i.e., numbers appearing at various places in the string?

Hi, no probably not but I will have occasions where there are numbers before the one I'm trying to extract.

[IM22]

Or without using INDIRECT()

Please Login or Register  to view this content.

Hi, thanks that works too.

[icestationzbra]

you mean something like:

000999randomtextstringhere12345678anotherrandomtextstringhere

my point is to check whether you will ever have situation where the numbers will be separated by alphabets.

[Ron Coderre]

With
A1 containing your text string with embedded numbers.....like: 1234randomtext135791351234anotherrandomtextstringhere
and
D1 containing the number length you want to find: 12

This regular formula returns the first number of that length found in A1

Please Login or Register  to view this content.

)

In the above example, that formula returns: 135791351234

is that something you can work with?

[canapone]

Hi,

another option to identify first 12 digits number in a string:

Please Login or Register  to view this content.

to be confirmed with control+shift+enter

Hope it helps

Regards

[IM22]

With
A1 containing your text string with embedded numbers.....like: 1234randomtext135791351234anotherrandomtextstringhere
and
D1 containing the number length you want to find: 12

This regular formula returns the first number of that length found in A1

Please Login or Register  to view this content.

)

In the above example, that formula returns: 135791351234

is that something you can work with?

Hi Ron,

Thanks for that but I got an error when I tried it. I simplified it so that I only had one column, A, with the data in it then I copied your formula directly into B1 and put 12 in D1. The error highlighted LEN in your formula.

[IM22]

Hi,

another option to identify first 12 digits number in a string:

Please Login or Register  to view this content.

to be confirmed with control+shift+enter

Hope it helps

Regards

Hi Canapone,

Thanks for your help, when I tried this I got a 0 in each result.

[Cutter]

Did you include a closing bracket on Ron's suggestion? It isn't showing in your quote in post #11 and in Ron's post (#9) it shows below the formula. It somehow escaped the code tags!

[Ron Coderre]

Hi Ron,

Thanks for that but I got an error when I tried it. I simplified it so that I only had one column, A, with the data in it then I copied your formula directly into B1 and put 12 in D1. The error highlighted LEN in your formula.

Hmmm...the only thing I see missing is the ending parenthesis after the final $D$1:

Please Login or Register  to view this content.

Did you try copying the formula from this web page?

[IM22]

Did you include a closing bracket on Ron's suggestion? It isn't showing in your quote in post #11 and in Ron's post (#9) it shows below the formula. It somehow escaped the code tags!

I didn't notice that, but when I tried it with the closing bracket I still get the same error.

[IM22]

Here's a screenshot of what I get:

Excel formula 12 digit number extract.png

[Ron Coderre]

I didn't notice that, but when I tried it with the closing bracket I still get the same error.

Here you go...Excel 2000/2003 sometime has an issue when there are multiple instances of INDEX in a formula.
Try this ARRAY FORMULA...committed with CTRL+SHIFT+ENTER (instead of just ENTER)

Please Login or Register  to view this content.

Does that work?

[Ron Coderre]

I didn't notice that, but when I tried it with the closing bracket I still get the same error.

And...Here's a regular formula that works:

Please Login or Register  to view this content.

Those INDEX functions can be really helpful to avoid C+S+E...But sometimes there's an art to placing them.

[Marcol]

...But sometimes there's an art to placing them.

Interesting Ron, always wondered about that feature in 2003. I thought it was just me being stupid!

A couple of notes on your last formula.
It returns a string not a number, this is easily solved by adding *1, however ...
1/. If the string has a string of digits more than the requested length then a truncated result is returned.
e.g.
Request 8 digits from "randomtextstringhereagain246824689anotherrandomtextstring", "24682468" will be returned.

2/. Because you are returning a string spaces seem to be treated as zero, so digits preceded with a space throw anomalies.
e.g.
Request 8 digits from "randomtextstringhereagain 246824689anotherrandomtextstring", " 2468246" will be returned.
If there are extra spaces the result changes
Request 8 digits from "randomtextstringhereagain(2 spaces)246824689anotherrandomtextstring", "(2 spaces)246824" will be returned.

This is not a criticism, just an observation, it might be irrelevant to the OP.
I'm looking for a more robust solution but am a bit pressed for time at the moment.

There are also anomalies with CANAPONEs' solution when the string of digits exceeds the requested length
e.g.
Request 8 digits from "randomtextstringhereagain246824689anotherrandomtextstring", 46824689 will be returned which is larger than 24682468.

If it's possible to have a decimal number in the string, well that's another kettle of fish!

See the attached

[pike]

can you use a udf with RegExp?

[Marcol]

This UDF will return the first group with the exact number of digits called.
Option Explicit

Please Login or Register  to view this content.

Enter as e.g.

Please Login or Register  to view this content.

See this thread this and for other possible solutions by DonkeyOte
Extract 9 digit number from string

[Ron Coderre]

"It returns a string not a number, this is easily solved by adding *1, however ..."
>>I'm assuming we're looking for some kind of reference "number" that may have preceeding zeros.

"1/. If the string has a string of digits more than the requested length then a truncated result is returned.
e.g.
Request 8 digits from "randomtextstringhereagain246824689anotherrandomtextstring", "24682468" will be returned."
>> I don't think we've been given the complete set of requirements, yet and we don't know the ultimate purpose of the formula.


"2/. Because you are returning a string spaces seem to be treated as zero, so digits preceded with a space throw anomalies.
e.g."
>> So far...no indication that spaces will be included. However, sometimes all we can do is toss out suggestions in hopes that one will be appropriate.

[Kyle123]

My preference (I like to keep things simple and complex formulas hurt my head )

Please Login or Register  to view this content.

[Marcol]

@ Kyle123
Again we don't know, but if there is a string of digits longer than the requested length, your UDF will return a truncated result.
e.g.
Request 8 digits from "randomtextstringhereagain246824689anotherrandomtextstring" ...

Please Login or Register  to view this content.

24682468 will be returned.
Post #21 will reject this possibility.

We need some feedback from the OP

[Kyle123]

@Marcol, good catch , hows this?

Please Login or Register  to view this content.

[IM22]

Thanks a lot to everyone who has offered advice.

Ron, your solutions both worked perfectly for my purposes, which as some were asking is to extract a 12 digit serial number from anywhere in a web URL. There would therefore be no spaces so that question does not matter to me although I can see why it would matter in other situations.

As a matter of interest (or not!), Ron's solution does not work if the text ends with the 12 digit number, it needs for there to be text following it but again that doesn't matter to me.

Thanks again to all, your help is much appreciated.

Ian

[Ron Coderre]

As a matter of interest (or not!), Ron's solution does not work if the text ends with the 12 digit number, it needs for there to be text following it but again that doesn't matter to me.

Ian

1) You're very welcome.
2) Thanks for spotting a flaw in the formula I posted.
This tweaked version resolves that issue:
B1: =MID(A1,MATCH(1,INDEX(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1)-$D$1+1)),$D$1)),0),0),$D$1)

[jindon]

Is there any way that I can target only say a 12 digit number in the text?

=GetNumber(A1,12)

Please Login or Register  to view this content.