Some String Functions in Excel VBA

String is an important variable type. I will try to discuss here about it. As the 'String' name suggest, it is used to hold strings of text. You have to work with Strings of text in Excel VBA a lot in next. So, it's time to learn in depth about it. I know now you will ask your mind, wait a minute, what things should you know about simple String variable? Don't it just store TEXT values? So, why "depth"?

Well, my answer is Yes. You are absolutely right. But in "depth" I mean some others option related to String, must know for better work. Simply use a Dim command to store a String Variable like below:

Dim x as String

And now while you are going to store a variable against the String variable simply use Double Quotes ("") mark like below:

Dim x as string
x = "This is String text" 

If you set a Date or Number or any others variable within Double Quotes ("") against a String variable, it definitely NOT store as Date or Number. It stores as TEXT and no mathematical calculation allowed here in this String variable.

Dim x as String
Dim y as String
Dim z as String

' All off these below variables are TEXT.
x = "This is Text"
y = "21-Mar-2018"
z = "215.36"

If I say about real life, then you may have few things to do with String variable. Assume that, You have a "Full Name" in A2 cell and you need to separate the "First Name" in B2 cell and "Last Name" in the C2 cell. Now what functions you need to use? Exactly many functions are related with this String variable, that you must know. Some popular String variable functions are:

• LCase
• UCase
• Trim
• Len
• Space
• Replace
• Left
• Right
• Mid
• ASC

These are few String functions but not a complete list. The are described as below:


LCase:

This String function will convert the text value into Lower Case text. For example in A1 Cell you have a sentence "A Quick Brown Fox Jumps Over The Lazy Dog". And in A3 cell you would like to return all in Lower Case character. Simple, use the below codes:

Sub LowerCaseText1()
    Dim x As String
    x = Cells(1, 1).Value
    Cells(3, 1).Value = LCase(x)
End Sub

Image 1: LCase

UCase:

This String function will convert the text value into Upper Case text. For example in A1 Cell you have a sentence "A Quick Brown Fox Jumps Over The Lazy Dog". And in A3 cell you would like to return all in Upper Case character. See the below codes:

Sub UpperCaseText1()
    Dim x As String
    x = Cells(1, 1).Value
    Cells(3, 1).Value = UCase(x)
End Sub

Image 2: UCase

Trim:

This String function will remove the unnecessary space from beginning and ending points of your sentence. For example in A1 Cell you have a sentence " A Quick Brown Fox Jumps Over The Lazy Dog ". If you use Trim, then it will return "A Quick Brown Fox Jumps Over The Lazy Dog like below:

Sub TrimText1()
    Dim x As String
    x = Cells(1, 1).Value
    Cells(3, 1).Value = Trim(x)
End Sub

Image 3: Trim

Len:

This String function will count the character used in a cell including space. For example in A1 Cell you have a sentence "       A Quick Brown Fox Jumps Over The Lazy Dog       ". The Len() will return 57 as Number like below:

Sub LenText1()
    Dim x As String
    x = Cells(1, 1).Value
    Cells(3, 1).Value = Len(x)
End Sub

Image 4: Len

Space:

It is not necessary that you have to remove unnecessary spaces from beginning and from ending point of your sentence. You might also need some free space in the beginning and in the ending point. Assume that you need 7 free space first of your "A Quick Brown Fox Jumps Over The Lazy Dog" sentence. Specify the space number in Space function and use it like below:

Sub SpaceText1()
    Dim x As String
    x = Cells(1, 1).Value
    Cells(3, 1).Value = Space(7) & x
End Sub

Image 5: Space

Replace:

With the Replace() you can replace an original text to correct text. Assume that, your "A Quick Brown Fox Jumps Over The Lazy Dog" wrongly written "Fox" instead of "Ox". Use the Replace function like below to change the word from "Fox" to "Ox" like below:

Sub ReplaceText1()
    Dim x As String
    x = Cells(1, 1).Value
    Cells(3, 1).Value = Replace(x, "Fox", "Ox")
End Sub

Image 6: Replace

Left:

The Left function used to chop characters from starting character number (1st) to mentioned character number (nth) right. Assume that, you wish to chop "A Quick Brown Fox" in A3 cell from "A Quick Brown Fox Jumps Over The Lazy Dog" text which is in A1 cell. Use the below code to chop it. Please note that, space is also included here and treated as a character.

Sub LeftText1()
    Dim x As String
    x = Cells(1, 1).Value
    Cells(3, 1).Value = Left(x, 17)
End Sub

Image 7: Left

Right:

The Right function used to chop characters from ending characters number (1) to mentioned character number (nth) left. Assume that, you wish to chop "The Lazy Dog" in cell A3 from "A Quick Brown Fox Jumps Over The Lazy Dog" text which is in A1 cell. Use the below code to chop it. Please note that, space is also included here and treated as a character.

Sub RightText1()
    Dim x As String
    x = Cells(1, 1).Value
    Cells(3, 1).Value = Right(x, 17)
End Sub

Image 8: Right

Mid:

This function is used to collect characters from middle part of a string. You just need to mention 2 things here. First is starting point of collecting, and Second is ending point of collecting data. For example, assume that, you wish to return "Fox Jumps" string in cell A3 from a string in cell A1 which is "A Quick Brown Fox Jumps Over The Lazy Dog". Now use the below codes:

Sub MidText1()
    Dim x As String
    x = Cells(1, 1).Value
    Cells(3, 1).Value = Mid(x, 15, 9)
End Sub

Image 9: Mid

InStr:

InStr is a short form of complete InString. It usually used to search a string within a string and returns the position number if matched. Assume that, A1 cell is "A Quick Brown Fox Jumps Over The Lazy Dog". Now you wish to search "Y" character. The inStr function will return 37 in A3 cell due to "Y" character matched in 37th position of A1 cell. It is case sensitiveness. "y" and "Y" is not same here.

Sub inStrText1()
    Dim x As String
    x = Cells(1, 1).Value
    Cells(3, 1).Value = Mid(x, "Y")
End Sub

Image 10: inStr

StrReverse:

As the function said, it returns the String as Reverse (from last to first). Assume that your A1 cell contains a string "A Quick Brown Fox Jumps Over The Lazy Dog". In A3 cell if you apply the StrReverse function, then it would return "goD yzaL ehT revO spmuJ xoF nworB kciuQ A".

Sub strReverseText1()
    Dim x As String
    x = Cells(1, 1).Value
    Cells(3, 1).Value = StrReverse(x)
End Sub

Image 11: StrReverse

ASC:

This VBA Function convert the single character into an ASCII Value. For example if you use ASC("x") then it will return 65.

Sub strReverseText1()
    Dim x As String
    x = "A"
    Cells(3, 1).Value = ASC(x)
End Sub

Image 12: StrReverse

There are 2 different types of Function used in Microsoft Excel. VBA and WorksheetFunction. We can use WorksheetFunctions in Excel VBA with a prefix WorksheetFunction and a dot. Later I will try to explain many WorksheetFunctions.

|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw