Separating Text and Numbers from an alpha-numeric String to separate columns

Friends, Many times you must have came across the situation where you have to separate numbers from the text or text from the numbers from the alpha-numeric string and these numbers or texts are not in the position where you could apply the great "text-to-column" approach as they neither possess fixed width nor separated by any thing and they are of various lengths.

Do you know that you can create your own functions in excel? Yes you can, just like SUM, MIN, MAX, TRIM, UPPER and many other functions. Today, We will use GetChars() function which will extract alphabets from an alpha-numeric string and GetNums()  which will get us only numbers.

I love practicals more than theory so just follow the steps mentioned below and see the magic.

We ll be using Excel 2010

+  Open MS Excel offcourse, and enable Developer toolbar which is not visible by default.
     Click File => Options => Customize Ribbon => You will see two panels on your right,look for
     the option Developer which is by default not selected, check it to enable the developer 
     toolbar on your ribbon.
+  On this toolbar, you will find the button Visual Basic, click it to open the VB Editor.
+  Open Insert and select  Module and copy paste the following code in it.


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
   
    MyStr = Left(MyStr, 5) & "-" & Mid(MyStr, 6, 7) & "-" & Right(MyStr, 1)
GoExit:
    GetNums = MyStr
End Function

+ Save the file and close this Module.


+ Insert another module and this time copy paste the following code in it.


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 = Trim(WorksheetFunction.Clean(MyStr))
End Function


+ Save this too and close VB editor.


Now you have made your own functions GetChars - for separating non-numeric characters and GetNums - for separating numeric characters from the alphanumeric string.

You can apply them just like you apply other function.

Example: If you have a alpha-numeric string in your cell A1 and you want to extract all the non-numeric characters of it in cell B1 and all the numeric characters in C1 then simply put this formula in B1

=GetChars(A1)

and this formula in C1

=GetNums(A1) 

Cheers