How to work with and modify 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
We can combine or join strings with the and '&' operator where we can mix variables and texts.
MsgBox (A & B & C)
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."
'Now let's take out the first part ("Please") from D.
MsgBox (Left(D, 6))
'Let's take out the last part ("Thx.")
MsgBox (Right(D, 4))
'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 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 a character with another one of your choosing.
MsgBox (Replace(D, " ", "-")) 'Please-Fix-Thx.
'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.
'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 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"))
'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.
MsgBox (UCase(D)) 'Upper case
'Reverse searching with InstrRev function
'InstrRev(stringcheck, stringmatch, [ start, [ compare ]])
MsgBox (InStrRev(D, "T")) 'returns position 12.