Format Concatenate Entries

  • Thread starter Thread starter DrHunter
  • Start date Start date
D

DrHunter

Hi All,

I am trying to concatenate different references into one Cell. However i
would like some part of the concatenation to be concatenated in different
ways eg bold, italics, underline and font size.

= A1 &" Bold Text " &A2 &" Italics Text " &A3 &" Different Font sized Text "

Thanks in advance.
 
Hi
unfortunately this is not possible within formulas. Formulas can only
return values - not formats.
 
You cant use character formatting on a cell with a function.

An alternative would be to use an event macro instead of a function. Put
this in the Worksheet code module (right-click on the worksheet tab and
choose View Code):


Private Sub Worksheet_Calculate()
Const BoldText As String = " Bold Text "
Const ItalicText As String = " Italic Text "
Const DifferentSizedFontText As String = _
" Different Sized Font "

Application.EnableEvents = False
With Range("B1")
.Value = Range("A1").Text & BoldText & _
Range("A2").Text & ItalicText & _
Range("A3").Text & DifferentSizedFontText
.Characters(InStr(.Text, BoldText), _
Len(BoldText)).Font.Bold = True
.Characters(InStr(.Text, ItalicText), _
Len(ItalicText)).Font.Italic = True
.Characters(InStr(.Text, _
DifferentSizedFontText)).Font.Size = 24
End With
Application.EnableEvents = True
End Sub
 
Back
Top