font formatting when concatenating?

[eHanSolo]

hi there,

was wondering if it's possible to apply formatting like bold, font colour, font size when i'm concatenating a few strings together??

thanks!


e

 

[xlbo]

not in the formula - if you pastespecial>values, you may then apply font formatting to different parts of the text


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Geoff is right, can't do it in formulae. But you could copy formatting in a VBA module for each character of the value of the concatenated strings.

Sub myjoin()
    Range("A3").Value = Range("A1").Value & Range("A2").Value
    For i = 1 To Range("A1").Characters.Count
        If Range("A1").Characters(i, 1).Font.Bold Then
            Range("A3").Characters(i, 1).Font.Bold = True
        End If
        If Range("A1").Characters(i, 1).Font.Italic Then
            Range("A3").Characters(i, 1).Font.Italic = True
        End If
        If Range("A1").Characters(i, 1).Font.Underline <> xlUnderlineStyleNone Then
            Range("A3").Characters(i, 1).Font.Underline = Range("A1").Characters(i, 1).Font.Underline
        End If
    Next
    For i = 1 To Range("A2").Characters.Count
        If Range("A2").Characters(i, 1).Font.Bold Then
            Range("A3").Characters(Range("A1").Characters.Count + i, 1).Font.Bold = True
        End If
        If Range("A2").Characters(i, 1).Font.Italic Then
            Range("A3").Characters(Range("A1").Characters.Count + i, 1).Font.Italic = True
        End If
        If Range("A2").Characters(i, 1).Font.Underline <> xlUnderlineStyleNone Then
            Range("A3").Characters(Range("A1").Characters.Count + i, 1).Font.Underline = Range("A2").Characters(i, 1).Font.Underline
        End If
    Next

End Sub

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.