Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Extract Separate Numbers From Letters

  1. #1
    Join Date
    7th January 2005
    Posts
    302

    Extract Separate Numbers From Letters

    I've found several posts but none seem to peform this varying function:
    EX12345678....Result in Col B: "EX" and Result in Col C: "12345678"
    RTZZ4567.......Result in Col B: "RTZZ" and Result in Col C: "4567"

    The problem with the formulas I've got specifically define - pulling let's say LEFT, 2 characters.....when, I may need it to pull 2 or 3 or 4.

    I found something that's smart enough to look for ONLY ALPHA and strip those out and place them into one column.
    =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

    * I need something that's smart enough to look for ONLY NUMERIC. no matter how long the string is...and place those in Column C (like I mention in the example at the top).

    Thanks in advance for your help!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,063

    Re: Extract Separate Numbers From Letters

    Hi

    Why not simply

    =RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1)

    Wigi
    Regards,

    Wigi

    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  3. #3
    Join Date
    7th January 2005
    Posts
    302

    Re: Extract Separate Numbers From Letters

    It almost works! Well, it does unless there's any odd characters at the end of the string such as this......
    Example:..............Result....(using your formula)
    XKR33333ZZ 33333ZZ

    Whereas, the formula used to extract alpha/text doesnt care whether there's characters mixed or not.....it simply stripped out ALL alphas no matter where they were placed within the string.

    Is there a way to change the formula you posted to: look throughout the whole string and pull both the XKR and the ZZ (any existing alpha if applicable) and provide the result of:

    Example:..............Result...
    XKR33333ZZ.........XKRZZ

    Ultimately, I'm trying to get this end result:
    All alpha in one column and all numeric in the next column:

    Example:..............Column A Result....Column B Result
    XKR33333ZZ.........XKRZZ.................33333............

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Extract Separate Numbers From Letters

    Looks a clear case for using VBA to me.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: Extract Separate Numbers From Letters

    Try these two functions.
    VB:
    Function GetChars(target As Range) 
        Dim MyStr As String, i As Integer 
        MyStr = "" 
        If Len(target.Value) = 0 Then Goto GoExit 
        For i = 1 To Len(target.Value) 
            If Not IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1) 
        Next i 
    GoExit: 
        GetChars = MyStr 
    End Function 
     
    Function GetNums(target As Range) 
        Dim MyStr As String, i As Integer 
        MyStr = "" 
        If Len(target.Value) = 0 Then Goto GoExit 
        For i = 1 To Len(target.Value) 
            If IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1) 
        Next i 
    GoExit: 
        GetNums = MyStr 
    End Function 
    
    
    For a value in cell A1, in B1 enter
    =GetChars(A1)
    and in C1 enter
    =GetNums(A1)

    EDIT: Note that the GetChars function retains spaces included in the original text. To easily remove leading and trailing spaces change the line of code
    VB:
    GetChars = MyStr 
    
    
    to
    VB:
    GetChars = Trim(MyStr) 
    
    
    Removing internal spaces requires more code.
    Last edited by thomach; February 1st, 2007 at 04:17.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  6. #6
    Join Date
    7th January 2005
    Posts
    302

    Re: Extract Separate Numbers From Letters

    The VBA option works beautifully!!! THANK YOU!!

    If there's a way to achieve it with a single formula/function/array; (meaning, 1 formula for the alpha and a different formula to use in the numeric column) .....I'd love to have that option as well -- to be able to give it to co-workers that need to clean up parts lists quickly and have no familiarity with VBA or add-ins.

    Chris

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    Re: Extract Separate Numbers From Letters

    I'm afraid I'm limited to a VBA answer on this one. But for what it's worth (I had to play some more), this version of the GetChars function eliminates all spaces and any formatting characters that might have been included in teh string (e.g., line ejects).
    VB:
    Function GetCharsNoSpaces(target As Range) 
        Dim MyStr As String, i As Integer 
        MyStr = "" 
        If Len(target.Value) = 0 Then Goto GoExit 
        For i = 1 To Len(target.Value) 
            If Not IsNumeric(Mid(target, i, 1)) And Asc(Mid(target, i, 1)) <> 32 Then MyStr = MyStr & Mid(target, i, 1) 
        Next i 
    GoExit: 
        GetCharsNoSpaces = Trim(WorksheetFunction.Clean(MyStr)) 
    End Function 
    
    
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  8. #8
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,299

    Re: Extract Separate Numbers From Letters

    Assuming that A2 contains the text string, and numbers occur together, maybe...

    B2:

    =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))))

    C2:

    =SUBSTITUTE(A2,B2,"")

    Hope this helps!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Letters To Represent Numbers
    By chris702564 in forum EXCEL HELP
    Replies: 3
    Last Post: June 30th, 2008, 10:31
  2. Extract All Letters Before Character
    By raghunandana in forum EXCEL HELP
    Replies: 4
    Last Post: March 6th, 2008, 14:30
  3. Convert Numbers To Letters. A=1, B=2 etc
    By Eryk in forum EXCEL HELP
    Replies: 10
    Last Post: February 16th, 2007, 01:43
  4. autofill using letters and numbers
    By breimer in forum EXCEL HELP
    Replies: 1
    Last Post: March 11th, 2006, 06:24

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