# Extract first 5 digit number starting from the right

1. ## Extract first 5 digit number starting from the right

Hi,

I am trying to extract a 5 digit part number from a cell that contains text and possibly multiple numbers. There is no set way that the numbers/spaces/commas will appear. The only sure thing is that the first 5 digit number starting from the right is the one I want.

I've attached a sheet where I've tried some formulas, but can't seem to figure it out.

Thanks

2. ## Re: Extract first 5 digit number starting from the right

Hi,

=MID(B3,LOOKUP(2,1/ISNUMBER(0+MID(B3,ROW(\$1:\$999),1)),ROW(\$1:\$999))-4,5)

Regards

3. ## Re: Extract first 5 digit number starting from the right

This works on most of them, depends what other data you have

=LOOKUP(TRUE,(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),5)+0)>10000,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),5)+0)

Source: http://www.mrexcel.com/forum/excel-q...t-strings.html
Post #3

Update: Go with XOR's suggestion - it's perfect!

4. ## Re: Extract first 5 digit number starting from the right

Try this user defined function

``Please Login or Register  to view this content.``
Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a C3, enter =GetNumber(B3) and copy down.

Remember to save the workbook as a macro enabled workbook .xlsm

5. ## Re: Extract first 5 digit number starting from the right

XOR,

This worked perfect!

I need to figure out what "LOOKUP(2,1/ISNUMBER" does, and i believe i understand what the formula is doing.

Thanks

6. ## Re: Extract first 5 digit number starting from the right

You're welcome!

Have a good weekend.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1