Formula to delete all characters except number string

Hey Guys,

I need a formula to remove all characters except for a 12 digit numeric string.

For example

Cell A1 contains: content="Zendaya 10-bulb Edison Chandelier with Bulbs, LD-4901, Warehouse of Tiffany, LD-4901, 15928877, 794504104264, compare Zendaya 10-bulb Edison Chandelier with Bulbs, best price on Zendaya 10-bulb Edison Chandelier with Bulbs, discounted Zendaya 10-bulb Edison Chandelier with Bulbs, cheap Zendaya 10-bulb Edison Chandelier with Bulbs"

Output needs to be: 794504104264

So to clarify I would prefer the formula to pull any numeric string of 12+ characters. Thanks in advance you guys!

you can use "Text to columns" feature (with space & comma as delimiters) to split the long string into individual blocks of string and then apply the Max function on the cells to extract the 12+ digit number. (This approach assumes that there would be only 1 such number in all the split strings).

• Say A1 contains the large string as above:
• Apply Text to Column with comma & space as delimiters.
• Check the box "Treat Consecutive delimiters as one".
• this particular string would split from A1 to AQ1.
• Now use the formula, MAX(1:1) to extract the number: 794504104264

HTH!

Flame,

The formula you provided works half the time. Two things that are happening.

1. The first and last number are sometimes dropped if the string starts or ends with a 0.

2. Sometimes no value is pulled and the output is a 0.

For example:
content="Athena 26-inch Swivel Counter Stool, AH225-26-AR-649, AH225-26-AR-649, 14257186, 739901002287, compare Athena 26-inch Swivel Counter Stool, best price on Athena 26-inch Swivel Counter Stool, discounted Athena 26-inch Swivel Counter Stool, cheap Athena 26-inch Swivel Counter Stool"

Output is = 0
Actual output should be:739901002287

Any additional help would be greatly appreciated. Thanks you guys

Book1.xlsx

I have attached the file with the content. Thanks again!

Hemesh,

Your solution works perfectly, only issue I could find is that it drops a 0 if the the string starts with a 0. Any easy fix? Thanks!

Try this.
I've noticed the pattern that the desired number is always followed by , compare

=TRIM(RIGHT(SUBSTITUTE(LEFT(A2,FIND(", compare",A2)-1)," ",REPT(" ",LEN(A2))),LEN(A2)))

There were a few cases where there were multiple consecutive numbers seperated by only a comma.
Such as 095385823894,095385798260,095385798307, compare in A30.

This will correct for that
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(A2,FIND(", compare",A2)-1),","," ")," ",REPT(" ",LEN(A2))),LEN(A2)))

Jonmo1.

Your formula worked perfectly. Thank you for everyone taking the time to look over this for me. Reputation will be added.

You're welcome.