Using Strings in VBA - Naukri Code 360

Introduction

This article will take you through the fundamentals, techniques, functions and utilities for performing string manipulations and operations 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.

String Variables

String variables act as the storage container for a group of characters.

Code

Dim vegetable As String
vegetable = "potato"
Range("A1").Value = vegetable

Result

Explanation

First line declares the variable vegetable as a string. The second line initializes the value of the vegetable as potato. Finally, we store the value of the vegetable in cell A1.

Instr Function

The ‘Instr’ function returns the position of a substring that is being searched in a given string. It is versatile and able to perform case-sensitive and case-insensitive searches in a given string.

Code

Dim stateString As String
stateString = "South Carolina"
MsgBox InStr(stateString, "o")
MsgBox InStr(7, stateString, "o")

Result

Explanation

The output here displays the values 2 and 10 since in the first statement, the string “o” is found at position 2 from the beginning of the string while for the second statement the string search starts at position 7 and the finds the searched substring “o” at pos 10.

Reverse Strings

Since there is no explicit function to reverse a string in Excel VBA, we would take a look at a short program that would be able to do that.

First, we will initialise four different variables out of which two would be string vars while the other two would be integers.

Code

Dim inputStr As String, reversedStr As String, length As Integer, i As Integer

Now we initialise two of the above variables (i.e. inputStr and length) with the following values:

Code

inputStr = InputBox("Enter the text you want to reverse")
length = Len(inputStr)

Now, we will use a For Next loop.

Code

For i = 0 To length - 1

Now we just take the last character from the string and place it at the front of ‘reversedStr’. 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.

Code

reversedStr = reversedStr & Mid(inputStr, (length - i), 1)

Now close the ‘for next loop’:

Code

Next i

Now we display reversedStr using a MsgBox

Code

msgbox reversedText

Result

Convert to Proper Case

In this program we will take a look at how can one convert text to the proper case in Excel VBA. We will try to convert the first letter of each word to uppercase and the remaining letters to lowercase.

The initial step here would be to declare two range objects. We call the Range objects rng and cell.

Code

Dim rng As Range, cell As Range

Now, we initialize the Range object rng with a selected range.

Code

Set rng = Selection

Next, we want to be able to check each cell in a randomly selected range (this range can be of any size). In Excel VBA, one can use the For Each Next loop in the following manner:

Code

For Each cell In rng
Next cell

Now, 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).

Code

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

Result

Count Words

This program will count the number of words on a selected range of cells.

Note: This program assumes that the words would be separated by one or more spaces.

The initial step here would be to declare two range objects and three variables. We call the Range objects rng and cell. One Integer variable we call cellStrings, one Integer variable we call totalStrings, and one String variable we call text.

Code

Dim rng As Range, cell As Range
Dim cellStrings, totalStringsAs Integer, text As String

Now, we initialize the Range object rng with a selected range and the two variables of type Integer with value 0.

Code

Set rng = Selection
cellStrings = 0
totalStrings = 0

Next, we want to be able to check each cell in a randomly selected range (this range can be of any size). In Excel VBA, one can use the For Each Next loop in the following manner:

Code

For Each cell In rng
Next cell

Now, 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).

Code

If Not cell.HasFormula Then
End If

We try to first write the contents of the cell to the variable ‘text’. 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.

Code

text = cell.Value
text = Trim(text)

Now, since a cell can still be empty therefore we assign the value 0 to the variable ‘cellStrings’ if it is and otherwise we don’t, and if it contains at least one word and we assign the value 1 to the variable ‘cellStrings’. Add the following code lines in your If statement.

Code

If text = "" Then
    cellStrings = 0
Else
    cellStrings = 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(text, " ") finds the position of the first space in content.

We will make use of the Do While Loop structure. The snippet that we placed between these words (the next three steps) will be repeated as long as the part after Do While is true. We want to repeat these steps as long as Instr(text, " ") > 0 is true (as long as text contains a space and thus more words). Add the Do While Loop in your If statement.

Code

Do While InStr(text, " ") > 0
Loop

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

Code

content = Mid(text, InStr(text, " "))

We trim the string again.

Code

text = Trim(text)

We increment cellWords by 1.

Code

cellWords = cellWords + 1

After having checked one cell, we add ‘cellStrings’ to the variable ‘totalStrings’. This code line should be placed outside the Do While Loop but in the If statement.

Code

totalStrings = totalStrings + cellStrings

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

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

Code

MsgBox totalStrings & " words found in the selected range."

Result

Separate Strings

This section of the article will take a look at a program that can be used to separate comma-separated strings in separate columns.

We start by declaring a variable called ‘name’ of type String, a variable called comma position of type Integer, and a variable called i of type Integer.

Code

Dim name As String, commapos As Integer, i As Integer

Here, for the cell with the value ‘Smith, Mike’, the comma is at position 6 while in the case of ‘Johnson, Matthew’ the comma is at position 9.

Here, we will use a loop to execute the operations on each name entered in Excel. First, we initialize the variable ‘name’ and use the ‘Instr’ function to find the position of the comma.

Code

For i = 2 To 7
    name = Cells(i, 1).Value
    commapos = InStr(name, ",")

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:

Code

Cells(i, 2).Value = Mid(name, commapos + 2)
Cells(i, 3).Value = Left(name, commapos - 1)

Mid(name, commapos + 2) means we want the part of the name starting at character 'commapos + 2' (this is exactly the first name).

Left(name, commapos - 1) means we want the part of the name starting at the beginning until the character 'commapos- 1' (this is exactly the last name).

We will finally close the loop

Code

Next i

Result

Explanation

The output here displays the values of the full name column separated out to ‘First Name’ and ‘Last Name’.

FAQs

1. How are strings concatenated/joined in Excel VBA?

Two or more strings can be joined together using the ‘&’ (ampersand) operator in excel VBA.

2. Name the variable used for storing Text.

String

Key Takeaways

Congratulations on finishing the blog!! After reading this blog, you will grasp the concept of the Strings, utility functions and their uses such as ‘Instr’, reversing strings etc. in Excel VBA.

Recommended problems -

If you are preparing yourself for the top tech companies, don't worry. Coding Ninjas has your back. Visit this link for a well-defined and structured material that will help you provide access to knowledge in every domain.