Tom’s Tutorials for Excel: Separate numbers and letters from alphanumeric string

Tom’s Tutorials for Excel: Separate numbers and letters from alphanumeric string.
You sometimes need to extract only numbers, or only letters, from an alphanumeric string.

For example if you have this:
HTRDFR9745 and you only want 9745

or you have
65UYDKWHDHSDUK and you only want 65

or trickier yet you have a mix like this:
FJ7R9yW5PXQ and you only want 795

Or, maybe you only want the non-digit characters extracted.

The following two User Defined Functions can accomplish this for you. See the picture for an example of their results in action, and green comments in the code that explain each step.



Function ExtractNumbers(strText As String)
'Declare the necessary variables.
Dim i As Integer, strDbl As String
'Loop through each character in the cell.
For i = 1 To Len(strText)
'If the character is a digit, append it to the strDbl variable.
If IsNumeric(Mid(strText, i, 1)) Then
strDbl = strDbl & Mid(strText, i, 1)
End If
Next i

'Each character in the cell has been evaluated, so you can define the
'ExtractNumbers function by setting it equal to the strDbl variable.
'The purpose of the CDbl function is to coerce the strDbl expression
'into a numeric Double data type.

ExtractNumbers = CDbl(strDbl)
End Function


Function ExtractLetters(strText As String)
'Declare the necessary variables.
Dim x As Integer, strTemp As String
'Loop through each character in the cell.
For x = 1 To Len(strText)
'If the character is not numeric, it must be a letter,
'so append it to the strTemp variable.

If Not IsNumeric(Mid(strText, x, 1)) Then
strTemp = strTemp & Mid(strText, x, 1)
End If
Next x

'Each character in the cell has been evaluated, so you can define the
'ExtractLetters function by setting it equal to the strTemp variable.

ExtractLetters = strTemp
End Function

    Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
15 comments on “Tom’s Tutorials for Excel: Separate numbers and letters from alphanumeric string
  1. Robert says:

    Tom,
    Is there a way to apply the extract macro formula to entire column? I tried the paste method and changed the cell to $A1 but it does not work accurately. I have a sheet of over a million rows. thnx

    • Tom Urtis says:

      So what you want to do is to apply this UDF to cells in, say, column B to extract just numbers, and in column C to extract just letters, from alphanumeric strings that are listed in column A?

      With the two UDFs I posted in this example pasted into a standard module in your workbook, stick these two macros into that (or a different) standard module in that same workbook:

      Sub OnlyNumbers()
      Dim LastRow As Long
      LastRow = Cells(Rows.Count, 1).End(xlUp).Row
      Range(“B1:B” & LastRow).FormulaR1C1 = “=ExtractNumbers(RC1)”
      End Sub

      Sub OnlyLetters()
      Dim LastRow As Long
      LastRow = Cells(Rows.Count, 1).End(xlUp).Row
      Range(“C1:C” & LastRow).FormulaR1C1 = “=ExtractLetters(RC1)”
      End Sub

      I just tested it and it works fine, no problem.

  2. Rizwana Thabassum says:

    above script is not working for me..

    • Tom Urtis says:

      “not working for me” tells me nothing about what you exactly did and what result you got, and what data you are working with. The UDF does work, as you can see from the pictures. Please re-read the instructions carefully about where to place the code and how to enter the function in your worksheet.

  3. Rizwana Thabassum says:

    i could find #NAME? in both B & C columns

  4. Brian says:

    This works awesome! I did have some issues figuring out how to actually insert the macro into the workbook, but figured out that creating a new macro, clearing out the macro field completely and then pasting the above macro worked great. Thanks!

  5. Sanket Sawant says:

    Requesting you to help me with the following
    Data is on left hand side. I want to split text in one column and numbers in another column.
    Please help
    Data text number
    abcd12a abcda 12
    abc12345asdd abcasdd 12345
    ab123dasfv
    bcd134dasdsgdg

    • Tom Urtis says:

      Based on your question, isn’t that what my example in this blog entry does? It looks at the original string, and with User Defined Functions per the code and formulas in the blog post, puts the text in one cell and the numbers in another cell. Maybe I am misunderstanding your question.

  6. Vishal says:

    Thanks for the great code ! It works like a charm. In addition to above , if I have a alphanumeric string beginning with 0 say for ex : 01234abc67 and I need the output to be 0123467 how do I get it ? Right now with the code , I do get the output as 123467. Looking forward to your reply . Thanks !

    • Tom Urtis says:

      Hi Vishal, I just returned from being on holiday and I see you sent this last week. Is this still an open issue that is not yet resolved, or have you already found a solution?

  7. ependra singh 9899808995 says:

    Extract numbuer in excel

    =npv (-0.9,,iferror (mid (A1,1+len (A1)-row (indirect (“1:”&len(A1))),1)%,””)))

    Please use it in excel

    • Tom Urtis says:

      Did you actually read my tutorial here?
      First, your formula has an error with the last “)” character not needed; it must be deleted for the formula to actually return a result.
      If you do that, your formula only returns the last digit if the last character is a digit. If not, your formula returns a #VALUE! error.
      Did you test your formula?
      When I tested your formula, with this string in A1
      y7gd^9ed4*74
      your formula returns 4 instead of what the objective is, to return (in this example) 79474.
      And when this string is in cell A1
      y7gd^9ed4*74x
      your formula returns #VALUE!
      Please explain why someone should use your [corrected without the trailing “)” character] formula for whatever purpose you have in mind.

  8. Rick Rothstein says:

    I have a different way to write the two functions in your article. Instead of repeated concatenations (causes VBA to continually find contiguous chunks of memory to copy each concatenation), I use something I call “memory stuffing”. The Mid function can also be used in statement form allowing you to stuff different character(s) into already existing assigned memory. Here are those functions…

    Function ExtractNumbers(ByVal strText As String) As String
    Dim X As Long
    For X = 1 To Len(strText)
    If Mid(strText, X, 1) Like “[!0-9]” Then Mid(strText, X) = ” ”
    Next
    ExtractNumbers = Replace(strText, ” “, “”)
    End Function

    Function ExtractLetters(ByVal strText As String) As String
    Dim X As Long
    For X = 1 To Len(strText)
    If Mid(strText, X, 1) Like “[!A-Za-z]” Then Mid(strText, X) = ” ”
    Next
    ExtractLetters = Replace(strText, ” “, “”)
    End Function

Leave a Reply

Your email address will not be published. Required fields are marked *

*