How to work with and modify texts (strings) in excel VBA?

Strings is just another word for text and in order to write strings you need to use the " quotation signs. This part will go through some practical ways to work with and modify strings to your specification. We will starting working with the classic analyst phrase. "Please Fix Thx." and modify that one throughout the examples on the page. Let's start with adding the text parts in to three strings.

A = "Please" B = "Fix" C = "Thx."

Below are some examples of your normal text operations

Join strings

We can combine or join strings with the and '&' operator where we can mix variables and texts.

'No spaces
MsgBox (A & B & C)

'With spaces
MsgBox (A & " " & B & " Thx.")

Now Let's add them all into one string D for further modifications.

'Let's make sure these are added into one string D.
D = A & " " & B & " Thx."

Left Syntax

'Now let's take out the first part ("Please") from D.
MsgBox (Left(D, 6)) 

Right Syntax

'Let's take out the last part ("Thx.")
MsgBox (Right(D, 4))

Mid Syntax

'Middle part can be taken out with the following ("Fix").
'Note here that our 7th character is a space.
'No point of including that, hence why I start at place 8th.
MsgBox (Mid(D, 8, 3))

Split Syntax

Split is a practical syntax for dividing a string into multiple ones based on a separator sign.

'Split our text string based on the space.
'The result will be stored in a array containing the 3 text parts we added originally.
MyArray = (Split(D, " "))
​
Debug.Print MyArray(0) 'Please
Debug.Print MyArray(1) 'Fix
Debug.Print MyArray(2) 'Thx.
​
'Add (0) for only returning the First textstring back which is "Please"
'Add (1) for the second etc.. just like a normal array.
MsgBox (Split(D, " ")(0)) 'Please

Replace Syntax

'Replace function
'Replace a character with another one of your choosing.
MsgBox (Replace(D, " ", "-")) 'Please-Fix-Thx.

Trim Syntax

'Trim Function
'Removes leading and trailing spaces.
MsgBox ("            " & D & "            ")
MsgBox (Trim("            " & D & "            "))

Now in order to test that the trim function worked other than looking at the size of the message boxes is with the Len syntax which gives us the number of characters in the string.

Len Syntax

'Length of string
MsgBox Len(("            " & D & "            ")) '39 characters.
MsgBox Len((Trim("            " & D & "            "))) '15 characters.

As we can see, the result was 15 characters instead of the 39 otherwise returned, which means that the Trim function worked like it is supposed to.

Instr Syntax

'Instr function similar to find where you want to find a substring in a string.
'InStr([ start ], string1, string2, [ compare ])
'InStr returns the startingplace for a substring. it is case sensitive.

'Fi from 'Fix' is being found at startingposition 8.
MsgBox (InStr(D, "Fi"))

'Asking my code to start looking from Characterposition 10 in "Please Fix Thx."
'This is returning 0 as the substring "Fi" is not found.
MsgBox (InStr(10, D, "Fi"))

LCase Syntax

'To sort out any instr issue of finding a text.
'Use Lcase which converts to Lowercase text.
'Alternatively you can use Ucase to convert to uppercase text.
MsgBox (LCase(D)) 'Lower case

Alternatively we can use UCase if want to convert it to uppercase text instead.

UCase Syntax

MsgBox (UCase(D)) 'Upper case

InstrRev Syntax

'Reverse searching with InstrRev function
'InstrRev(stringcheck, stringmatch, [ start, [ compare ]])
MsgBox (InStrRev(D, "T")) 'returns position 12.