concatenated cell properties

[Elfego Baca]

I have 5 cells filled:
A1 regular
A2 bold
A3 italic
A4 bold italic
A5 underline

Cell A6 uses the formula =CONCATENATE(A1," ",A2," ",A3," ",A4," ",A5)

This creates the following in Cell A6 regular bold italic bold italic
underline

I would like to be able to control the properties of each part of the
concatenated cell so that the word bold is a bold font, the word italic
is in an italic font, the words bold italic are in a bold and italic
font and the word underline is underlined. How do I put htis in the
formula to get each word done properly?

 

[Gord Dibben]

You don't.

Formulas return values only.

With some exceptions...............

=TEXT(A1,"dddd,mmmm,yyyy")

="Payment due " &TEXT(A1,"mmmm, d yyyy") & " " & TEXT(B1,"$#,##0.00")

But not the formatting you want.


Gord Dibben MS Excel MVP

 

[JE McGimpsey]

You can't - formulas return values, but can't format cells.

To do this you'd need to use an event macro instead of a formula. One
way (put this in your worksheet code module: right-click the worksheet
tab and choose View Code):

Private Sub Worksheet_Calculate()
Dim rDest As Range
Dim sTemp As String
Dim i As Long
Dim nPos As Long
Dim nLen As Long

On Error GoTo ErrorHandler
Application.EnableEvents = False
Set rDest = Range("A6")
rDest.ClearFormats
With Range("A1:A5")
For i = 1 To .Count
sTemp = sTemp & " " & .Cells(i).Text
Next i
rDest.Value = Mid(sTemp, 2)
nPos = 1
For i = 1 To .Count
sTemp = LCase(.Cells(i).Text)
nLen = Len(sTemp)
If nLen > 0 Then
With rDest.Characters(nPos, nLen).Font
.Bold = InStr(sTemp, "bold")
.Italic = InStr(sTemp, "italic")
If InStr(sTemp, "underline") Then _
.Underline = xlUnderlineStyleSingle
End With
nPos = nPos + nLen + 1
End If
Next i
End With
ErrorHandler:
Application.EnableEvents = True
End Sub


Note that this will only update when you calculate the sheet. You could
instead use the Worksheet_Change() method if you wished.