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
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
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.
above script is not working for me..
“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.
i could find #NAME? in both B & C columns
See my reply to your first message.
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!
Thanks for your nice message!
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
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.
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 !
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?
Extract numbuer in excel
=npv (-0.9,,iferror (mid (A1,1+len (A1)-row (indirect (“1:”&len(A1))),1)%,””)))
Please use it in excel
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.
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