Extract first 5 digit number starting from the right

**Cboggie** · 05-09-2014, 12:46 PM

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

**XOR LX** · 05-09-2014, 12:52 PM

Hi,

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

Regards

**Special-K** · 05-09-2014, 12:53 PM

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!

**mrice** · 05-09-2014, 12:59 PM

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

**Cboggie** · 05-09-2014, 01:10 PM

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

**XOR LX** · 05-09-2014, 01:17 PM

You're welcome!

Have a good weekend.

Extract first 5 digit number starting from the right

LinkBack

Thread Tools

Rate This Thread

Display

Extract first 5 digit number starting from the right

Re: Extract first 5 digit number starting from the right

Re: Extract first 5 digit number starting from the right

Re: Extract first 5 digit number starting from the right

Re: Extract first 5 digit number starting from the right

Re: Extract first 5 digit number starting from the right

Thread Information

Users Browsing this Thread

Similar Threads

How to extract a 15 or 16 digit number from an email.

[SOLVED] Extract Exact 6 Digit Number From String

Extract 9 digit number from string

Extract 10 digit number from string

[SOLVED] Extract 2, 3, 4 or 5-digit number from string

Bookmarks

Bookmarks

Posting Permissions