Excel like a finance pro.
VBA · Syntax
String functions
Left, Right, Mid, Len, Trim, UCase, Replace, InStr, Split: the VBA versions of the text functions.
When to use it
VBA has the same text tools as the sheet under slightly different names: Left, Right, Mid, Len, Trim, UCase, LCase, Replace, InStr, and Split.
The code
- Code
parts = Split("a,b,c", ",") MsgBox UCase(Trim(name))
Worked examples
Split
parts = Split("a,b,c", ",") MsgBox parts(0) → "a": Split returns a 0-based array
Split text.
Normalize
MsgBox UCase(Trim(name)) → Trimmed, uppercase
Normalize input.
Contains
If InStr(1, s, "West", vbTextCompare) > 0 Then → Case-insensitive contains test
InStr returns a position or 0.
Worth knowing
- Replace(s, " ", "") strips spaces; Trim only removes leading and trailing ones.
- Join is the reverse of Split.
- StrComp compares with a chosen case rule.
Where it goes wrong
- InStr returns 0 for not found, not an error.
- Mid in VBA is also a statement that can overwrite part of a string.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.