none
Using Excel I seek to embolden surname in string 'surname, first name' RRS feed

  • Question

  • In excel I have cell for surname and a second cell for first name. I seek to join them together separated by a comma but to only have the surname in bold. I can join them together but everytime I try to make one of the names bold they all go bold. I have tried making the surname cell bold before joing the two names together into one cell but they still all turn Bold or not at all.

    Help

    < ghfhazell@gmail.com>

    Wednesday, November 2, 2016 11:44 PM

All replies

  • Hi GeoHaz,

    For your requirement, I am afraid there is no direct way to achieve. I suggest you bold the combined string text, and then unbold the text what you did not want.

    Here is a simple code:

    Sub test()
           Dim s1 As String
        Dim s2 As String
         s1 = Range("C1").Text
         s2 = Range("D1").Text
        ActiveCell.Value = s1 & "," & s2
    With ActiveCell.Characters(Start:=1, Length:=Len(s1)).Font
            .Name = "??"
            .FontStyle = "Bold"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
        With ActiveCell.Characters(Start:=4, Length:=Len(s2)).Font
            .Name = "??"
            .FontStyle = "Regular"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
    End With
    End Sub

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 3, 2016 5:32 AM
  • I have tried this code and it seems to embolden the first 3 letters of s1 but if I remove the second part relating to s2 it would seem to embolden the whole of s1 and leave s2 as Regular i.e does what I am seeking to do.

    I have about 300 records containing s1 and s2. Do I merely expand range < s1 = Range("C1:C300").Text > and <s2 = Range("D1:D300").Text> or is this likely to produce some odd results or should I introduce "Dim yx As Range etc and if so I should be grateful of your help with this feature. Many thanks. GeoHaz

    Thursday, November 3, 2016 4:48 PM
  • Hi GeoHaz,

    >> f I remove the second part relating to s2 it would seem to embolden the whole of s1 and leave s2 as Regular i.e does what I am seeking to do.

    Did you mean the above code did not work for you? I assume it might be related with your real data.

    >> Do I merely expand range < s1 = Range("C1:C300").Text > and <s2 = Range("D1:D300").Text>
    For this, I suggest you try something like below, you may need to change it according your above solution for C1 & D1.

    Sub test()
        Dim s1 As String
        Dim s2 As String
        For i = 1 To 300
         s1 = Range("C" & i).Text
         s2 = Range("D" & i).Text
        Range("E" & i).Value = s1 & "," & s2
    '    With Range("E" & i).Characters(Start:=1, Length:=Len(s1)).Font
    '        .Name = "??"
    '        .FontStyle = "Bold"
    '        .Size = 11
    '        .Strikethrough = False
    '        .Superscript = False
    '        .Subscript = False
    '        .OutlineFont = False
    '        .Shadow = False
    '        .Underline = xlUnderlineStyleNone
    '        .ThemeColor = xlThemeColorLight1
    '        .TintAndShade = 0
    '        .ThemeFont = xlThemeFontMinor
    '    End With
        With Range("E" & i).Characters(Start:=4, Length:=Len(s2)).Font
            .Name = "??"
            .FontStyle = "Regular"
            .Size = 11
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0
            .ThemeFont = xlThemeFontMinor
        End With
        Next i
    End Sub

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 4, 2016 2:47 AM