VBA Tip: Formatting Characters Within a Cell

To modify the formatting of specified characters, use the Characters object:

Range("A1").Characters(START_NUM, NUM_CHARS)

Practice Example

In this example, the macro will return the first 2-3 characters in italics and the name of the city or town in bold:

Code for the macro:

Sub test()

    For rowNum = 1 To 12
    
        'Cell contents
        textCell = Cells(rowNum, 1)
        
        'Same contents split into three parts and saved in an array 
        textArray = Split(textCell, " ")
        
        'Length of part 1
        length1 = Len(textArray(0))
        
        'Length of part 2
        length2 = Len(textArray(1))
        
        'Set ITALICS for Part 1
        Cells(rowNum, 1).Characters(1, length1).Font.Italic = True
        
        'Set BOLD for Part 2
        Cells(rowNum, 1).Characters(length1 + 2, length2).Font.Bold = True

    Next

End Sub