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!

Updated Jan 2021

 

Steps

1. Copy in VBA code

2. Use new function

3. Done

 

 

Watch a 3 minute tutorial video, or read on and download below!

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. 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, however it could not handle decimals. 23.5 was processed as 235 and the decimal was processed as 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
        Else
            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. 

Next step is to add this useful function to your Personal Macro Workbook.
Also since you got rid of that pesky text, use your data to create a Linear Forecast Excel Model!

Your numbers and text are organised, now work on your note taking skills with a Note Taking Excel Template.

 

4. Download

More templates