Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Extract Digits From Text

1. I agreed to these rules
Join Date
13th November 2006
Location
Australia
Posts
8

## Extract Digits From Text

This is my first post, so please be gentle with me!

I have a string in a cell that contains a name, a 3-4 character alpanumeric code and then a 4-6 digit number, each separated by a space. An example or two:

J Bloggs SPEC 123456
J Smith AG06 9364
J & K Brown SWP 358686

I need to be able to have in a new cell the 4-6 digit number at the right of the cell.

Could someone please give a formula that will separate the data I need? I've tried numerous ways without success.

Many thanks!

Buff

Excel Video Tutorials / Excel Dashboards Reports

2. Senior Member
Join Date
19th October 2006
Location
Australia
Posts
287

## Re: Extracting Text From A String

To do this accurately, you need to be able to access the VBA.StrReverse function. You can only access the VBA.StrReverse function with a user defined function:

VB:
```Public Function lastchunk(ByRef s As String)
lastchunk = Right(s, InStr(1, VBA.StrReverse(s), " ") - 1)
End Function

```
Put this in a new module for the workbook that you are working on, and then on the worksheet, in the column next to the one that you want to extract the "last chunk" from, type:
=lastchunk(-reference the cell in here-)

Regards,

G.

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: Extracting Text From A String

See Extract Number From Text

Or, use

=IF(ISNUMBER(--RIGHT(A1,6)),--RIGHT(A1,6),IF(ISNUMBER(--RIGHT(A1,5)),--RIGHT(A1,5),--RIGHT(A1,4)))

4. I agreed to these rules
Join Date
13th November 2006
Location
Australia
Posts
8

## Re: Extract Digits From Text

Thanks for the replies. I've gone with Dave's solution. Thanks to you both.

Buff

Excel Video Tutorials / Excel Dashboards Reports

5. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Extract Digits From Text

Hi,

=MID(A1,LOOKUP(9.999999999E+307,FIND(" ",A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))+1,255)

HTH

##### 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
•
porno