Working With and Modifying Texts in Excel VBA?

To write text, aka strings, you must use the “quotation signs”. This part goes through various practical techniques to work with and modify strings to your specification.

Using the classic analyst phrase: “Please Fix Thx”, the following are examples of ways to modify strings:

  • Adding the Text to Three Strings

A = “Please” B = “Fix” C = “Thx.”

With the and & operator to be able to mix texts and variables- for example,

‘No spaces
MsgBox (A & B & C)
&
‘With spaces
MsgBox (A & ” ” & B & ” Thx.”)

Then, for further modifications, add them all into one string D-

D = A & ” ” & B & ” Thx.”

  • Left Syntax
Take "Please" out from D'
MsgBox (Left(D, 6)) 
  • Right Syntax
Take out "Thx."
MsgBox (Right(D, 4))
  • Mid Syntax
The middle part can be removed with "Fix". 
MsgBox (Mid(D, 8, 3))
  • Split Syntax

This is a practical syntax to divide a string into several ones that are based on a separator sign.

Split the string based on the space and the result will be in an array including the original three text parts. 
MyArray = (Split(D, " "))
  • Replace Syntax
Replace function, a character, and another text of your choice
  • Trim Syntax
Trim function by removing the trailing and leading spaces. 
  • Len Syntax

This syntax is an alternative way the trim function can work to allow the number of characters in the string.

'Length of string
MsgBox Len(("            " & D & "            ")) '39 characters.
MsgBox Len((Trim("            " & D & "            "))) '15 characters.
  • Instr Syntax
Instr function is similar in finding what you desire to find a substring. 
'InStr([ start ], string1, string2, [ compare ])
  • LCase Syntax and UCase Syntax
'LCase is used for any find text issue and to covert to lowercase text. 

As an alternative, you can use UCase to convert it to uppercase text.

MsgBox (UCase(D)) 'Upper case
  • InstrRev Syntax
Using the InstrRev function to reverse searching 

Conclusion

This text, aka strings, is pretty straightforward and easy to use. You must use the “quotation signs”. This part goes through various practical techniques to work with and modify strings to your specification. And whether you use a classic phase or more elaborate, the steps are pretty straightforward. Nevertheless, for more information, please feel free to consult the site.

Tags: