VBA Tricks : Format Characters in a Cell

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

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

Practice Example

start - format characters in a cell

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

objective - format characters in a cell

Code for the macro :

Sub test()

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

    Next

End Sub