Tag Archives: start

[Cheatsheet] VBA String Functions

Function What Does it do? Example Result
Len(str) Returns the length of the string Len(“Hello”) 5
Ucase(str) Changes the string to upper case Ucase(“Hello”) HELLO
Lcase(str) Changes the string to lower case Lcase(“Hello”) hello
Left(str, num) Shorten the string from the left Left(“Hello”,3) Hel
Right(str, num) Shorten the string from the right Right(“Hello”,3) llo
Mid(str, start, length) Extract the middle of a string Mid(“Hello”,2,2) el
Instr(start, look in this string, find this string) Find if a string is in another string Instr(0,”Hello there”,”Hello”) 1
Trim(str) Removes preceding and trailing spaces Trim(” hello there “) hello there
LTrim(str) Removes spaces from the beginning of a string Ltrim(” hello “) hello
Rtrim(str) Removes spaces from the end of a string Rtrim(” hello “) ¬†hello
String(num, char) Repeats the char num times String(2, “a”) aa
Space(num) Makes a certain num of spaces Space(2)
Cint(str) Change a string to a integer Cint(111.1) 111
Clng(str) Change a string to a long (use if you get an overflow error) Clng(111.1) 111
Cdbl(str) Change a string to a double Cdbl(111.1) 111.1
Split(str,delimiter) Split a string into an array Split(“Hello,There,Friend”, “,”) (0) = Hello
(1) = There
(2) = Friend
Join(array, delimiter) Join an array into a string Join(array(“Hello”, “There”, “Friend”),”,”) Hello,There,Friend

Basic Regular Expression Patterns for Beginners

This is super simplified, but it’s enough to get started with. I remember being confused by the gigantic tables with all the special clauses, so when you’re beginning keep it simple.

Remember if you want to match a literal character you can just type it out, unless there’s a special character. Special characters have meanings in regex patterns, so you need to type a \ in front of them if you want to specifically match them in the pattern.

Common special characters are: + . \ – [] {} $ ? |

Example: If I wanted to match taste, taster, tasters, I would type a pattern like taste\w{0,2}

If I wanted to match taste!, I would write a pattern like taste\!

Code What does it Match?
\d Numbers
\w Word characters like letters of any case
\s Spaces
\b Boundary of a word character
\D Not a number
\W Not a word character
\S Not a space
\n Line break
\r Line break
\t Tab
? Previous code is optional
* Repeat previous code 0 to infinity
+ Repeat previous code once or more
{1} Repeat previous code once (can use any number)
{1,2} Repeat previous code 1 to 2 times (can use any numbers)