String Manipulation in Excel VBA

Join Strings  |  Left  |  Right  |  Mid  |  Len  |  Instr

In this chapter, you’ll find the most important functions to manipulate strings in Excel VBA.

Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet.

Join Strings

We use the & operator to concatenate (join) strings.

Code:

Dim text1 As String, text2 As String
text1 = “Hi”
text2 = “Tim”

MsgBox text1 & ” ” & text2

Result:

Note: to insert a space, use ” ”

Left

To extract the leftmost characters from a string, use Left.

Code:

Dim text As String
text = “example text”

MsgBox Left(text, 4)

Result:

Right

To extract the rightmost characters from a string, use Right. We can also directly insert text in a function.

Code:

MsgBox Right(“example text”, 2)

Result:

Mid

To extract a substring, starting in the middle of a string, use Mid.

Code:

MsgBox Mid(“example text”, 9, 2)

Result:

Note: started at position 9 (t) with length 2. You can omit the third argument if you want to extract a substring starting in the middle of a string, until the end of the string.

Len

To get the length of a string, use Len.

Code:

MsgBox Len(“example text”)

Result:

Note: space (position 8) included!

Instr

To find the position of a substring in a string, use Instr.

Code:

MsgBox Instr(“example text”, “am”)

Result:

Note: string “am” found at position 3.

Separate Strings

Below we will look at a program in Excel VBA that separates strings.

Situation:

Place a command button on your worksheet and add the following code lines:

1. First, we declare a variable called fullname of type String, a variable called commaposition of type Integer, and a variable called i of type Integer.

Dim fullname As String, commaposition As Integer, i As Integer

The problem we are dealing with is that we need to tell Excel VBA where we want to separate the string. In case of Smith, Mike the comma is at position 6 while in case of Williams, Janet the comma is at position 9.

2. We use a loop to execute the operations on each name entered in Excel. First, we initialize the variable fullname. Next, we use the Instr function to find the position of the comma.

For i = 2 To 7
fullname = Cells(i, 1).Value
commaposition = InStr(fullname, “,”)

3. Finally, we want to write the part after the comma to column B and the part in front of the comma to column C. You can achieve this by adding the lines:

Cells(i, 2).Value = Mid(fullname, commaposition + 2)
Cells(i, 3).Value = Left(fullname, commaposition – 1

Mid(fullname, commaposition + 2) means we want the part of fullname starting at character ‘commaposition + 2’ (this is exactly the first name).

Left(fullname, commaposition – 1) means we want the part of fullname starting at the beginning until character ‘commaposition- 1’ (this is exactly the last name).

4. Don’t forget to close the loop.

Next i

5. Add six names separated by a comma and space to Range(“A2:A7”).

6. Test the program.

Result:

Reverse Strings

Below we will look at a program in Excel VBA that can reverse strings.

Situation:

Place a command button on your worksheet and add the following code lines:

1. First, we declare four variables. One variable called text of type String, one variable called reversedText also of type String, one variable called length of type Integer, and one variable called i of type Integer.

Dim text As String, reversedText As String, length As Integer, i As Integer

2. We initialize two variables. We use the InputBox function to get a text string from the user. We use the Len function in Excel VBA to get the length of a string.

text = InputBox(“Enter the text you want to reverse”)
length = Len(text)

3. We start a For Next loop.

For i = 0 To length – 1

4. Now comes the simple trick. We take the last character from text and place it at the front of ReversedText. We can use the Mid function in Excel VBA to extract a character from a string. We use the & operator to concatenate (join) two strings.

reversedText = reversedText & Mid(text, (length – i), 1)

5. Don’t forget to close the loop.

Next i

Example: text = “Car”. The length of text is 3. For i = 0 to 2, we extract the substring of text starting at position length – i with length 1. Thus, for i = 0, Mid(text, 3, 1) equals r. We place r at the first position of reversedText. For i = 1, Mid(text, 2, 1) equals a. We add a to reversedText which becomes ra. For i  = 2, Mid(text, 1, 1) equals C. We add C to reversedText which becomes raC.

6. Finally, we display reversedText using a MsgBox.

msgbox reversedText

7. Test the program.

Result:

Convert to Proper Case

Below we will look at a program in Excel VBA that converts text to proper case. That is, the first letter in each word in uppercase, and all other letters in lowercase.

Situation:

1. First, we declare two Range objects. We call the Range objects rng and cell.

Dim rng As Range, cell As Range

2. We initialize the Range object rng with the selected range.

Set rng = Selection

3. We want to check each cell in a randomly selected range (this range can be of any size). In Excel VBA, you can use the For Each Next loop for this. Add the following code lines:

For Each cell In rng

Next cell

Note: rng and cell are randomly chosen here, you can use any names. Remember to refer to these names in the rest of your code.

4. To ignore a cell that contains a formula, add the following code line between For Each and Next (only if cell.HasFormula is false we continue).

If Not cell.HasFormula Then

End If

5. Next, we want to convert each word in this range to ‘proper case’. You can use the worksheet function Proper for this task. Add the following code line in your if statement.

cell.Value = WorksheetFunction.Proper(cell.Value)

6. Test the program.

Result:

Count Words

Below we will look at a program in Excel VBA that counts the number of words in a selected range. One or more spaces are assumed to separate words.

Situation:

1. First, we declare two Range objects and three variables. We call the Range objects rng and cell. One Integer variable we call cellWords, one Integer variable we call totalWords, and one String variable we call content.

Dim rng As Range, cell As Range

Dim cellWords, totalWords As Integer, content As String
2. We initialize the Range object rng with the selected range and the two variables of type Integer with value 0.

Set rng = Selection
cellWords = 0
totalWords = 0

3. We want to check each cell in a randomly selected range (this range can be of any size). In Excel VBA, you can use the For Each Next loop for this. Add the following code lines:

For Each cell In rng

Next cell

Note: rng and cell are randomly chosen here, you can use any names. Remember to refer to these names in the rest of your code.

4. Next, we determine for each cell in this range how many words it contains. To ignore a cell that contains a formula, add the following code line between For Each and Next (only if cell.HasFormula is false we continue).

If Not cell.HasFormula Then

End If

5. First, we write the content of the cell to the variable content. Next, we remove the spaces at the beginning and the end (if there are any). In Excel VBA, you can use the Trim function for this. For example, ” excel vba” will be converted to “excel vba”. Add the following code lines in your If statement.

content = cell.Value
content = Trim(content)

Note: the trim function in Excel VBA does not remove extra spaces between words, but that’s OK in this example.

6. At this point, a cell can still be empty. If the cell is empty, we assign the value 0 to the variable cellWords. If not, it contains at least one word and we assign the value 1 to the variable cellWords. Add the following code lines in your If statement.

If content = “” Then
cellWords = 0
Else
cellWords = 1
End If

A cell can contain more than one word of course. That’s exactly what we want to find out now. As an example we take: “excel vba”. If a cell contains at least one space at this stage, it contains at least one more word. You can use the Instr function in Excel VBA to look for a space. Instr(content, ” “) finds the position of the first space in content.

7. We will make use of the Do While Loop structure. Code placed between these words (at step 8, 9 and 10) will be repeated as long as the part after Do While is true. We want to repeat these steps as long as Instr(content, ” “) > 0 is true (as long as content contains a space and thus more words). Add the Do While Loop in your If statement.

Do While InStr(content, ” “) > 0

Loop

8. Next, we take the part of content starting at the position of the first space. We use the Mid function for this.

content = Mid(content, InStr(content, ” “))

For example: Mid(“excel vba”, InStr(“excel vba”, ” “)) will give ” vba”.

9. We trim the string again.

content = Trim(content)
Result: “vba”

10. We increment cellWords by 1.

cellWords = cellWords + 1

This Do While Loop will be repeated as long as content contains a space and thus more words. In our example, we exit the Do While Loop since “vba” does not contain a space anymore! Result: this cell contains 2 words.

11. After having checked one cell, we add cellWords to the variable totalWords. This code line should be placed outside the Do While Loop but in the If statement.

totalWords = totalWords + cellWords

The whole process starts again for the next cell until all cells have been checked.

12. Finally, we display the value of totalWords using a msgbox. This code line should be placed outside the For Each Next loop.

MsgBox totalWords & ” words found in the selected range.”

13. Test the program.

Result:

Leave a Reply

Your email address will not be published.