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!

 

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. 

 

4. Download

Name
Name
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