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?

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

On Sat, 10 Feb 2007 12:31:31 -0500, Elfego Baca <(E-Mail Removed)> wrote:

>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?

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.


In article <5PWdnZ61u-(E-Mail Removed)>,
Elfego Baca <(E-Mail Removed)> wrote:

> 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?