Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Extract Separate Numbers From Letters

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

  • #2
    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.com ==> English articles ==> Excel memes

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

    Comment


    • #3
      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............

      Comment


      • #4
        Re: Extract Separate Numbers From Letters

        Looks a clear case for using VBA to me.
        .

        Comment


        • #5
          Re: Extract Separate Numbers From Letters

          Try these two functions.
          Code:
          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
          Code:
          GetChars = MyStr
          to
          Code:
          GetChars = Trim(MyStr)
          Removing internal spaces requires more code.
          Last edited by thomach; February 1st, 2007, 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.

          Comment


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

            Comment


            • #7
              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).
              Code:
              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.

              Comment


              • #8
                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!

                Comment

                Trending

                Collapse

                There are no results that meet this criteria.

                Working...
                X