Find and return 10-digit number from string

[legendary_popsicle]

Hey all,

I am working with data where I need to extract a 10-digit number from a string. Where the 10-digit number is in the string varies, so I can't use a simple left/len combination. Here's an example below:

something and email@me.com 1234567890 stuff things
​

The formula I am using now is this:

=MID(F4,MIN(FIND({0,1,2,3,4,5,6,7,8,9},F4&"0123456789")),10)
​

This finds the first number in a string and returns that number and the next 9 digits for a total of a 10-digit string (so it would return 1234567890). However, I run into an issue when I have a string that has numbers in the email address.

So this:

number in email123@me.com 1234567890 example
​

Would return this:

123@me.com
​

Is there a way to modify the formula above so that it searches for 10 consecutive numbers and not just the first number in a string?

Thank you all!

 

[shg]

A slight mod to fairwinds formula:

=LOOKUP(9E307, --MID(F9,ROW(INDIRECT("1:" & LEN(F9) - 9)), 10))

 

[legendary_popsicle]

Only you know your data, but as long as there are no other 10 (or longer) digit numbers in your text, you can use this array-entered** formula to retrieve the 10-digit number...

=MAX(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),10)),--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),10)))

**Commit this formula using CTRL+SHIFT+ENTER, not just Enter by itself

Hey Rick, this one seems to work perfectly. I know it is asking a lot, but would you mind explaining it in detail so I can understand each piece and replicate if needed in the future?

 

[fairwinds]

=LOOKUP(9.99999999999999E+307,--MID(SUBSTITUTE(A1&"x"," ","x"),ROW(INDIRECT("1:"&LEN(A1))),10))

 

[legendary_popsicle]

Anyone care to explain any of their final formulas? I would love to understand in order to replicate for the future.

Thanks in advance.

 

[fairwinds]

I will if you tell me if I finally got mine to work for you.

If not I will go and hide somewhere instead.

 

[legendary_popsicle]

I will if you tell me if I finally got mine to work for you.

If not I will go and hide somewhere instead.

Oh sorry! Yes, your final one worked perfectly. I've been fiddling around with it trying to understand how it works. I can't say that I've used this combination of functions before.

 

[pgc01]

Hi Fairwinds, nice to see you!

Your formula did not work for me for some cases like:

abc 1234567.90 ef
abc 123456,890 ef

and others similar when a valid number has other characters than digits.

If I'm not mistaken all the digits in the 10 digit character string must be tested.

Another option:

=MID(A1,MATCH(12,MMULT(--(--ISNUMBER(-MID("x"&A1,ROW(INDIRECT("1:"&LEN(A1)-9))+{0,1,2,3,4,5,6,7,8,9,10,11},1))={0,1,1,1,1,1,1,1,1,1,1,0}),{1;1;1;1;1;1;1;1;1;1;1;1}),0),10)

 

[legendary_popsicle]

Hi Fairwinds, nice to see you!

Your formula did not work for me for some cases like:

abc 1234567.90 ef
abc 123456,890 ef

and others similar when a valid number has other characters than digits.

If I'm not mistaken all the digits in the 10 digit character string must be tested.

Another option:

=MID(A1,MATCH(12,MMULT(--(--ISNUMBER(-MID("x"&A1,ROW(INDIRECT("1:"&LEN(A1)-9))+{0,1,2,3,4,5,6,7,8,9,10,11},1))={0,1,1,1,1,1,1,1,1,1,1,0}),{1;1;1;1;1;1;1;1;1;1;1;1}),0),10)

Maybe I'm doing something wrong, but that formula doesn't seem to work for numbers that include a comma or decimal. That's okay though; for this particular circumstance, there won't be any 10-digit numbers with other characters.

 

[pgc01]

Maybe I'm doing something wrong, but that formula doesn't seem to work for numbers that include a comma or decimal. That's okay though; for this particular circumstance, there won't be any 10-digit numbers with other characters.

Hi

No, you are doing nothing wrong. I wrongly understood that you wanted a string of 10 digits. If you want a 10 character number that may include other characters than digits, like a comma or a dot, then Fairwinds formula is the correct one.

 

[fairwinds]

Thanks for feedback! Glad you got it to work.

As promised i will try to explain how the formula works:


<TABLE style="WIDTH: 648pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=864><COLGROUP><COL style="WIDTH: 648pt; mso-width-source: userset; mso-width-alt: 31597" width=864><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 648pt; HEIGHT: 30pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=40 rowSpan=2 width=864>=LOOKUP(9.99999999999999E+307,--MID(SUBSTITUTE(A1&"x"," ","x"),ROW(INDIRECT("1:"&LEN(A1))),10))


</TD></TR><TR style="HEIGHT: 15pt" height=20></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2918277 height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>The red part will substitute all spaces with an x and put an x at the end of the string. This to isolate the string.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>The green part will create an array of numbers {1,2,3… used to indicate the position where to look for the string</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20>The blue part will extract an array of 10 digit strings, from the string with starting point based on the array above I.e. Char {1-10, 2-11,3-12...</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>Doing a Lookup with this very big number on this array of strings will return the last (rightmost) 10 digit string which is 100% numbers</TD></TR></TBODY></TABLE>


Hope this makes sense.