Converting Specific Words to Lowercase Using VBA in Excel

In this lesson, we will go through a VBA macro that converts specific words in an Excel worksheet to lowercase. This macro is useful when you need to standardize the case of certain words across your worksheet. We will break down the code line by line to help you understand how it works.

This VBA macro is designed to process a given range in an Excel worksheet and convert specific words to lowercase. These words are defined in a string and then split into an array for comparison with the words in each cell.

Here is the full code

Sub ConvertCertainWordsToLowerCase()

    Dim WS As Worksheet
    Dim WrdArray() As String
    Dim IndividualWords() As String
    Dim CertainWordsString As String
    Dim ResultString As String
    Dim i As Integer
    Dim j As Integer
    
    Set WS = Worksheets("Sheet1")
    
    ' Define the words to convert to lowercase
    CertainWordsString = "with,and,or,for,to,from"
    WrdArray() = Split(CertainWordsString, ",")
    
    ' Loop through each cell in the used range
    For Each MyCell In ActiveSheet.UsedRange
        If MyCell.Value <> "" Then
            MyCell.Value = Trim(MyCell.Value)
            IndividualWords() = Split(MyCell.Value)
            
            ' Loop through words in the cell
            For i = LBound(IndividualWords) To UBound(IndividualWords)
                ' Loop through target words
                For j = LBound(WrdArray) To UBound(WrdArray)
                    ' Compare and convert to lowercase if match is found
                    If StrComp(IndividualWords(i), WrdArray(j), vbTextCompare) = 0 Then
                        IndividualWords(i) = LCase(WrdArray(j))
                        Exit For
                    End If
                Next j
            Next i
            
            ' Reassemble the sentence
            ResultString = ""
            For i = LBound(IndividualWords) To UBound(IndividualWords)
                ResultString = ResultString & " " & IndividualWords(i)
            Next i
            
            MyCell.Value = Trim(ResultString)
        End If
    Next
    
    MsgBox "Completed!", vbInformation, ""
    
End Sub

Below is the step-by-step explanation of the above code.

Declaring Variables

Dim WS As Worksheet
Dim WrdArray() As String
Dim IndividualWords() As String
Dim CertainWordsString As String
Dim ResultString As String
Dim i As Integer
Dim j As Integer

• WS: This variable holds the reference to the worksheet.
• WrdArray(): An array to store the specific words that need to be converted to lowercase.
• IndividualWords(): An array to hold the words from each cell.
• CertainWordsString: A string containing the specific words, separated by commas.
• ResultString: A string to build the final result after processing each cell.
• i, j: Integer variables used as counters in the loops.

Setting the Worksheet

Set WS = Worksheets("Sheet1")

This line sets the worksheet (Sheet1) to the variable WS.

Defining Certain Words

CertainWordsString = "with,and,or,for,to,from"
WrdArray() = Split(CertainWordsString, ",")

• CertainWordsString: Contains the words that need to be converted to lowercase.
• Split: This function splits the string into an array, using the comma as a delimiter.

Loop Through Each Cell in the Used Range

For Each MyCell In ActiveSheet.UsedRange
    If MyCell.Value <> "" Then

This loop goes through each cell in the used range of the active sheet. If the cell is not empty, it proceeds to process the cell's value.

Trim and Split Cell Value

MyCell.Value = Trim(MyCell.Value)
IndividualWords() = Split(MyCell.Value)

• Trim: Removes any leading or trailing spaces from the cell's value.
• Split: Splits the cell value into an array of words.

Compare and Convert Words

'Loop through words of sentence
For i = LBound(IndividualWords) To UBound(IndividualWords)
    'Loop through certain words
    For j = LBound(WrdArray) To UBound(WrdArray)
        'Compare two array elements
        If StrComp(IndividualWords(i), WrdArray(j), vbTextCompare) = 0 Then
            IndividualWords(i) = WrdArray(j)
            Exit For
        End If
    Next j
Next i

• Outer Loop: Iterates through each word in the cell.
• Inner Loop: Iterates through each word in the certain words array.
• StrComp: Compares the words, ignoring case (vbTextCompare).
• If Condition: If a match is found, the word is converted to lowercase, and the inner loop exits.

Reconstructing the Sentence

ResultString = ""
'create result sentence
For i = LBound(IndividualWords) To UBound(IndividualWords)
    ResultString = ResultString & " " & IndividualWords(i)
Next i
MyCell.Value = Trim(ResultString)

• Loop: Concatenates each word from the array into ResultString.
• Trim: Ensures there are no leading or trailing spaces in the final result.

Final Message

MsgBox "Completed!", vbInformation, ""

Displays a message box indicating that the macro has completed its execution.

This is the result you will get when run the above macro.

This VBA macro provides an efficient way to standardize the case of specific words in your Excel worksheet. By understanding each line of the code, you can modify and expand it to fit your specific needs.

How to Add a Space After Every Comma in Excel Using VBA

In today's post, I'm going to show you how to add a space after every comma in cells in Excel using VBA. This can be particularly useful when formatting text data to improve readability. For instance, if you have a list of phrases separated by commas without spaces, you can use this simple VBA code to add a space after each comma.

Let’s consider this example worksheet.

Here's the complete code:

Sub AddSpaceAfterComma()

Dim MyCell As Range
Dim SpaceRemovedString As String
Dim CorrectedString As String

For Each MyCell In ActiveSheet.UsedRange

    If MyCell.Value <> "" Then
            
        MyPhrase = MyCell.Value
               
        CorrectedString = Replace(MyPhrase, ",", ", ")
        
        'put corrected sentence in relevant cell
        MyCell.Value = CorrectedString
        
    End If
    
Next

MsgBox "Completed!", vbInformation, ""

End Sub

Let's go through the code step-by-step.

First, the code starts by looping through each cell in the used range of the active sheet:

For Each MyCell In ActiveSheet.UsedRange

Inside the loop, it checks if the cell is not empty:

If MyCell.Value <> "" Then

If the cell is not empty, it stores the value of the cell in a variable called MyPhrase:

MyPhrase = MyCell.Value

Next, it uses the Replace function to find every comma in the phrase and replace it with a comma followed by a space:

CorrectedString = Replace(MyPhrase, ",", ", ")

After correcting the string, it puts the corrected string back into the cell:

MyCell.Value = CorrectedString

Finally, after the loop is done, it displays a message box to indicate that the process is complete:

MsgBox "Completed!", vbInformation, ""

By following these steps, you can easily add a space after every comma in each cell in your used range, making your text data cleaner and more readable. This is the result you will get when you run the above macro.

Improved Version to Handle Existing Spaces

Sometimes, some phrases may already have spaces after the commas. If this happens, the result will have two spaces after the comma.

To avoid this, we can first remove all spaces after the commas and then add a single space again. This ensures consistent formatting without double spaces.

Here's the improved version of the code:

Sub AddSpaceAfterComma_V2()

Dim MyCell As Range
Dim SpaceRemovedString As String
Dim CorrectedString As String

For Each MyCell In ActiveSheet.UsedRange

    If MyCell.Value <> "" Then
            
        MyPhrase = MyCell.Value
        
        'First remove all the spaces after comma
        MyPhrase = Replace(MyPhrase, ", ", ",")
               
        CorrectedString = Replace(MyPhrase, ",", ", ")
        
        'put corrected sentence in relevant cell
        MyCell.Value = CorrectedString
        
    End If
    
Next

MsgBox "Completed!", vbInformation, ""

End Sub

In this improved version, the key change is the addition of a step to first remove any spaces after commas. This is done by:

MyPhrase = Replace(MyPhrase, ", ", ",")

This step ensures that any existing spaces after commas are removed, preventing the creation of double spaces when we add the new space. After this step, the rest of the process is the same as in the first version, where we add a space after each comma and put the corrected string back into the cell. This ensures that there is exactly one space after each comma, regardless of the original formatting.