Extracting number from text, no consistency on where number is located in text

**yamalady** · 05-09-2019, 11:57 AM

Need a formula to extract the 8 digit number from a string of text....which there is no consistency of where the number is located within the text.
(attachment button isn't working for me, so I am pasting the text below)
So for example, I need to extract 73004790 out of the first line.

Product Title
YardSmart 73004790 Decorative Screen Panel 2X4-Sprig, White
Boerboel 73024430 Heavy Duty Drop Rod
Boerboel Gate Solutions 73014253 Heavy-Duty Modern Wrap Hinge Pair, Black
Boerboel Gate Solutions 73024418 - GardDog Locking Latch Two-Sided - Locking Latch
Boerboel Gate Solutions 73014305 24 Heavy-Duty Drop Rod Black
Boerboel 73014317 Self Closing Device
Boerboel Gate Solutions 73014300 Pair Heavy-Duty Contemporary Wrap Hinge Black
Boerboel Gate Solutions 73002218 Heavy-Duty Gate Latch Black
Boerboel Heavy-Duty Modern Wrap Hinge Kit 73014301 Black(2 Pack)
Boerboel Standard Chain Link/Farm Hinge 73014314 Silver(2 pack)
YardSmart 73012418 Select Rail Square Bal Vinyl Railing, 6' x 36', White
YardSmart 3.5 to 6.5 4-Piece Post Trim Base 73018131

**Fluff13** · 05-09-2019, 12:07 PM

If your happy with a UDF how about

Please Login or Register  to view this content.

Used like

Formula:

Please Login or Register  to view this content.

**Bernie Deitrick** · 05-09-2019, 12:10 PM

Array-enter this formula for a string in A2 (enter using Ctrl-Shift-Enter)

=SUM(IFERROR(VALUE(MID(SUBSTITUTE(A2&"x"," ","x"),ROW(INDIRECT("A1:A" &LEN(SUBSTITUTE(A2&"x"," ","x")))),8)),0))

Note that if you ever have a 9 - or longer - digit number then it will return an incorrect result.

**AlKey** · 05-09-2019, 12:17 PM

Try this
Enter in B2 and copy down

Formula:

Please Login or Register  to view this content.

v	A	B
1
2	YardSmart 73004790 Decorative Screen Panel 2X4-Sprig, White	73004790
3	Boerboel 73024430 Heavy Duty Drop Rod	73024430
4	Boerboel Gate Solutions 73014253 Heavy-Duty Modern Wrap Hinge Pair, Black	73014253
5	Boerboel Gate Solutions 73024418 - GardDog Locking Latch Two-Sided - Locking Latch	73024418
6	Boerboel Gate Solutions 73014305 24 Heavy-Duty Drop Rod Black	73014305
7	Boerboel 73014317 Self Closing Device	73014317
8	Boerboel Gate Solutions 73014300 Pair Heavy-Duty Contemporary Wrap Hinge Black	73014300
9	Boerboel Gate Solutions 73002218 Heavy-Duty Gate Latch Black	73002218
10	Boerboel Heavy-Duty Modern Wrap Hinge Kit 73014301 Black(2 Pack)	73014301
11	Boerboel Standard Chain Link/Farm Hinge 73014314 Silver(2 pack)	73014314
12	YardSmart 73012418 Select Rail Square Bal Vinyl Railing, 6' x 36', White	73012418
13	YardSmart 3.5 to 6.5 4-Piece Post Trim Base 73018131	73018131

Extracting number from text, no consistency on where number is located in text

LinkBack

Thread Tools

Rate This Thread

Display

Extracting number from text, no consistency on where number is located in text

Re: Extracting number from text, no consistency on where number is located in text

Re: Extracting number from text, no consistency on where number is located in text

Re: Extracting number from text, no consistency on where number is located in text

Thread Information

Users Browsing this Thread

Similar Threads

Extracting text before number(s)

Extracting numbers and text from inconsistent text/number formatted string

[SOLVED] extracting number from cell contains both text and number alternatively

Extracting number from text

Extracting a number from a text string and treating it as a number (NOT text)

[SOLVED] Extracting a 'number' from text

Extracting the number from a text

Bookmarks

Bookmarks

Posting Permissions