Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Extract Digits From Text

  1. #1
    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. #2
    Join Date
    19th October 2006
    Location
    Australia
    Posts
    287

    Re: Extracting Text From A String

    Welcome Buff Daddy!

    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. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    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. #4
    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. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Import Text And Digits With Different Delimiters
    By Alexat in forum Excel General
    Replies: 2
    Last Post: January 9th, 2008, 09:08
  2. Join Text Based on Value of Left Digits
    By paul_laz in forum Excel General
    Replies: 7
    Last Post: July 6th, 2007, 01:35
  3. Extract Two Digits From Number
    By Timbo in forum Excel General
    Replies: 4
    Last Post: October 18th, 2006, 20:44
  4. Extract specific text from a text file
    By Phlu in forum Excel General
    Replies: 4
    Last Post: June 23rd, 2006, 01:30

Bookmarks

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