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
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.
2. Building The Sheet
1. Design your layout.
Create 3 columns - Raw Data, Number, Text
Put your raw data in the Raw Data column.
2. Enter VBA code
Alt+F11 to bring up the VBA editor. Right click on "ThisWorkbook", click "Insert", click "Module".
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.
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.
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.