Table of Contents
Example
Consider this dataset containing a mix of alphabets and numbers, now I want to ignore all the text and fetch only the numbers or vice-a-versa, I may want to ignore all the numbers and fetch only the text. For this there is no ready-made formula in excel. Though we can use one of the array function methods to extract this information but that’s a learning for another day. Today we are going to solve this by creating User Defined Functions (UDFs) in VBA.
Steps to insert code
- Open the Visual Basic Editor using Alt + F11
- Goto Insert Tab
- Click on Module
- Paste the below code into module
GetNumbers
The first function we shall create is get numbers. This shall allow us to extract numbers only from an entire cell. The logic is we shall write a code where excel will iterate through each character and verify if it’s a numeric value or text value. If it’s a number it will be retained else ignored.
Code
Function GetNumbers(data As String) As String
Dim i As Integer
Dim onlynumbers As String
For i = 1 To Len(data)
If IsNumeric(Mid(data, i, 1)) = TRUE Then
onlynumbers = onlynumbers & Mid(data, i, 1)
End If
Next i
GetNumbers = onlynumbers
End Function
GetText
This shall allow us to extract text only from an entire cell. The logic is we shall write a code where excel will iterate through each character and verify if it’s a numeric value or text value. If it’s a text it will be retained else ignored.
Code
Function GetText(data As String) As String
Dim i As Integer
Dim onlytext As String
For i = 1 To Len(data)
If IsNumeric(Mid(data, i, 1)) = FALSE Then
onlytext = onlynumbers & Mid(data, i, 1)
End If
Next i
GetText = onlynumbers
End Function
Using the Function
We can now switch over to excel and goto any cell, type =GetText or GetNumbers and provide a cell reference or data within double quotes and it shall return with only text or only numbers respectively.
Note:
Now, if we look at the GetText function entire logic is the same except the above line and a variable name. The below line indicates that we want to retain the character if IsNumeric returns false. Earlier we evaluated if IsNumeric returns true, since at that point we wanted numbers while now we want non-numeric values. This change from true to false ensures we retain non numeric values.
Import Code into your file
You can directly import the above code into your Excel file by following the below steps:
- Download the code file from here
- Open the Visual Basic Editor using Alt + F11
- Goto File Menu
- Click on Import File
- Browse to the folder path where the downloaded file is saved
- Click on Open
- The code is now available in your file.
Any Excel workbook in which macros are written should be saved in .xlsm (Excel Macro-Enabled workbook)format.
