Easily split out numbers and text 

Large messy Excel data sets can be difficult to analyse. People often enter in measurements in one cell for example -  34.4kgs. This means you have to split it up before running functions like average.  

The VBA below allows you to easily split it up, regardless of decimals, whole numbers, or if the text is left or right!



1. Copy in VBA code

2. Use new function

3. Done



1. The Objective

I ran into a data-set 250,000 rows long that looked like the snippet to the left, which are measurements of chemicals used. My boss wanted to know how many chemicals were used in certain months. 

Clearly my first step was to try and get at the numbers, which were horrifically entered in. Some were decimals, some had the text to the left, some to the right. 

After researching online I found a piece of VBA that nearly did it, which you can find here. However it could not handle decimals, 23.5 was output as 235 and the decimal joined the text. 

By now I had the code that almost did what I wanted, so a little bit of editing and consulting with the fantastic Excel community (which I discussed in my blog), I got it to do exactly what I wanted.


chem usage.png

2. Building The Sheet

1. Design your layout.
Create 3 columns - Raw Data, Number, Text
Put your raw data in the Raw Data column. 

Layout - Number and Text Split.png

2. Enter VBA code
Alt+F11 to bring up the VBA editor. Right click on "ThisWorkbook", click "Insert", click "Module".

Split Text and Numbers VBA 1.png


Double click on "Module 1". Copy in the code below: 

Option Explicit
Public Function Strip(ByVal x As String, LeaveNums As Boolean) As Variant
Dim y As String, z As String, n As Long
For n = 1 To Len(x)
y = Mid(x, n, 1)
If LeaveNums = False Then
If y Like "[A-Za-z ]" Then z = z & y 'False keeps Letters and spaces only
If y Like "[0-9. ]" Then z = z & y 'True keeps Numbers and decimal points
End If
Next n
Strip = Trim(z)
End Function

Save your workbook. You will have to save it as a Macro Enabled Workbook. 

xlsm save.png

3. Using the VBA code
In the first row of your Number column, add in the formula - =NUMBERVALUE(Strip(A2,TRUE)) 
In the first row of your Text column, add in the formula - =Strip(A2,FALSE)

Drag the two formulas down to the bottom of your dataset. 

Split text and numbers formulas.png

3. Summary

Easy as that - you now have workable numbers split out! This fantastic way of doing it hasn't let me down yet and saved a lot of time. 


4. Download

Helps me to design future templates that you will find relevant
Let me know if you need help, and I would be thrilled to learn what you are using this template for!

More templates