Day 7 — Text functions - cleaning messy data

Most of the data you will ever automate arrives dirty. These eight functions fix nearly all of it.

The measuring and cutting functions

Function	Gives you	On "Data Analytics"
`Len(t)`	How many characters	14
`Left(t, n)`	First n characters	`Left(t, 4)` is Data
`Right(t, n)`	Last n characters	`Right(t, 9)` is Analytics
`Mid(t, start, n)`	n characters from position start	`Mid(t, 6, 3)` is Ana

Sub Cutting()
    Dim t As String
    t = "Data Analytics"

    Debug.Print Len(t)
    Debug.Print Left(t, 4)
    Debug.Print Right(t, 9)
    Debug.Print Mid(t, 6, 3)
    Debug.Print Mid(t, 6)
End Sub

VBA counts from 1 The first character is position 1, not 0. Mid(t, 1, 1) is the first letter. Leave the length off Mid and it runs to the end of the string.

InStr - where is it?

InStr hunts for text inside text and returns the position it starts at, or 0 if it is not there at all.

Sub Finding()
    Dim t As String
    t = "anita.sharma@example.com"

    Debug.Print InStr(t, "@")
    Debug.Print InStr(t, "zzz")
    Debug.Print Left(t, InStr(t, "@") - 1)
    Debug.Print Mid(t, InStr(t, "@") + 1)
End Sub

That last pair is the classic move: find the marker, then cut either side of it. Splitting an email, a code with a dash in it, a "Surname, First" name - all the same shape.

Always check for 0 first Left(t, InStr(t, "@") - 1) on a string with no @ becomes Left(t, -1), and real Excel throws a run-time error. Test If InStr(t, "@") > 0 Then before you cut.

Cleaning

Sub Cleaning()
    Debug.Print "[" & Trim("   spaces both ends   ") & "]"
    Debug.Print UCase("north")
    Debug.Print LCase("NORTH")
    Debug.Print Replace("DEL-2025-0042", "-", "/")
    Debug.Print Replace("1,25,000", ",", "")
End Sub

Trim only removes spaces at the two ends, never the ones in the middle. Replace changes every occurrence, not just the first.

Comparing text safely

"north" = "North" is False in VBA. Import data from three different systems and you will meet this on your first day.

Sub CaseTrouble()
    Debug.Print "north" = "North"
    Debug.Print UCase("north") = UCase("North")
    Debug.Print UCase(Trim("  north  ")) = "NORTH"
End Sub

The habit worth forming When you compare text that came from somewhere else, wrap both sides in UCase(Trim( )). It costs nothing and removes two whole classes of "but it looks the same" bug.

Split - one string into many pieces

Split chops text wherever it finds your separator and hands back an array. Day 10 goes into arrays properly; for now, treat it as a numbered list starting at 0.

Sub Splitting()
    Dim parts As Variant
    parts = Split("North,Anita,Laptop", ",")

    Debug.Print parts(0)
    Debug.Print parts(1)
    Debug.Print parts(2)
    Debug.Print "Pieces:", UBound(parts) + 1
    Debug.Print Join(parts, " | ")
End Sub

Putting it to work on the sheet

Real job: build a short code for every order from the region and the product - first letter of the region, first three of the product, then the row.

Sub BuildCodes()
    Dim i As Long
    Dim lastRow As Long
    Dim code As String

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("I1").Value = "Code"

    For i = 2 To lastRow
        code = UCase(Left(Cells(i, 3).Value, 1)) & _
               UCase(Left(Cells(i, 5).Value, 3)) & _
               "-" & Cells(i, 1).Value
        Cells(i, 9).Value = code
    Next i

    Debug.Print "Row 2 code:", Cells(2, 9).Value
    Debug.Print "Row 8 code:", Cells(8, 9).Value
End Sub

Those underscores at the end of the lines are VBA line continuations - they let one statement run across several lines so it stays readable. There must be a space before the underscore and nothing after it.

Try these yourself

Print the first three letters of every salesperson name, rows 2 to 17.
From "ORD-2025-0042", pull out just 2025 using InStr and Mid.
Write a macro that puts the salesperson name in UPPER CASE into column J.
Use Split on "a-b-c-d" and print how many pieces came back.
Explain why you should test InStr for 0 before using its answer in Left or Mid.

← PreviousDay 6 — The last row, Do loops and For Each Next →Day 8 — Numbers, rounding and dates