How to make only first letter of words in a text string bold

    Hi. Does anyone know what code to use or how to make only the first letter of the words in a text string in bold font in specific cells in excel?


    For example:
    instead of - Alpha Numeric
    I want it to be Alpha Numeric


    Would appreciate some help. Thanks.

    Code
    cells(1,1)="John Doe"


Range("A1").Characters(1, 1).Font.Bold = True
Range("A1").Characters(6, 1).Font.Bold = True

    Here is one approach, if your text is in A1.
    [VBA]Sub x()


    Dim oMatches As Object, i As Long, vOut As Variant, r As Range


    Set r = Range("A1")


    With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\b(\w+)"
    If .Test(r) Then
    Set oMatches = .Execute(r)
    ReDim vOut(0 To oMatches.Count - 1)
    For i = 0 To oMatches.Count - 1
    vOut(i) = oMatches(i).firstindex + 1
    Next i
    For i = LBound(vOut) To UBound(vOut)
    r.Characters(vOut(i), 1).Font.Bold = True
    Next i
    End If
    End With


    End Sub[/VBA]

    Can I replace A1 with the whole column for the range as in:


    Set r = Range("B:B")


    Although right now I'm trying and it's not working

    [VBA]Sub x()


    Dim oMatches As Object, i As Long, vOut As Variant, r As Range


    For Each r In Range("B1", Range("B" & Rows.Count).End(xlUp))
    With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\b(\w+)"
    If .Test(r) Then
    Set oMatches = .Execute(r)
    ReDim vOut(0 To oMatches.Count - 1)
    For i = 0 To oMatches.Count - 1
    vOut(i) = oMatches(i).firstindex + 1
    Next i
    For i = LBound(vOut) To UBound(vOut)
    r.Characters(vOut(i), 1).Font.Bold = True
    Next i
    End If
    End With
    Next r


    End Sub


    [/VBA]



    StephenR,


    If you have in a cell:
    One two Three
    Your code result is:
    One two Three

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login