Concatenate Text Formating

[Lunch]

I'm working with a workbook is used for template for creating
documents. A lot of information needs to be displayed in a specific
format (IE Italics/Bold/Underline etc). For example one of the things
I need to do is provide the following information in a single cell
under a table where SHEET1!A1 is a cell reference for a sample size.

Note: N = SHEET1!A1; M = Mean; SD = Standard Deviation.

The problem is that I am not able to get the cell call to work and
when I try and concatenate various cells together I lose the
formating. I'm sure there has to be a way to do this..........suggestions???

 

[Gary''s Student]

You can't capture the formating when the result is the display of a
concatenation formula.

 

[Lunch]

You can't capture the formating when the result is the display of a
concatenation formula.

Surely there is another way to get something like this to work

 

[Ron Rosenfeld]

I'm working with a workbook is used for template for creating
documents. A lot of information needs to be displayed in a specific
format (IE Italics/Bold/Underline etc). For example one of the things
I need to do is provide the following information in a single cell
under a table where SHEET1!A1 is a cell reference for a sample size.

Note: N = SHEET1!A1; M = Mean; SD = Standard Deviation.

The problem is that I am not able to get the cell call to work and
when I try and concatenate various cells together I lose the
formating. I'm sure there has to be a way to do this..........suggestions???

Is the entire cell formatted the same, or are do you want different parts of
the cell formatted differently?

You are probably going to require VBA routines to accomplish the formatting.
--ron

 

[Lunch]

Is the entire cell formatted the same, or are do you want different parts of
the cell formatted differently?

You are probably going to require VBA routines to accomplish the formatting.
--ron

THe entire cell is not formated the same. Some of it is bold/
underline another part of it is italics

 

[NickHK]

It would be easier if you did not use Excel as a word processor, but you can
still achieve you aim.
You cannot use a worksheet formula to concatenate, as then you cannot format
the individual characters.
You may need to add more Font/character characteristic if needed:

Private Sub CommandButton1_Click()
Dim SourceCells As Range
Dim DestRange As Range
Dim Cell As Range
Dim SourceChar As Long
Dim DestChar As Long
Dim SourceFont As Font

Const DELIM As String = "; "

Set SourceCells = Range("A1:C1")
Set DestRange = Range("A2")

DestRange.ClearContents

'Build the string first
For Each Cell In SourceCells
DestRange.Value = DestRange.Value & DELIM & Cell.Value
Next

DestRange.Value = Mid(DestRange.Value, Len(DELIM) + 1)

'Now process each Char
For Each Cell In SourceCells
For SourceChar = 1 To Cell.Characters.Count
Set SourceFont = Cell.Characters(SourceChar, 1).Font
DestChar = DestChar + 1

With DestRange.Characters(DestChar, 1).Font
.Bold = SourceFont.Bold
.ColorIndex = SourceFont.ColorIndex
.FontStyle = SourceFont.FontStyle
.Name = SourceFont.Name
.Size = SourceFont.Size
.Underline = SourceFont.Underline
' Other properties ?
End With
Next
DestChar = DestChar + Len(DELIM)
Next

NickHK

 

[Ron Rosenfeld]

THe entire cell is not formated the same. Some of it is bold/
underline another part of it is italics

In that case, instead of a formula, you will need to use VBA to concatenate the
string; insert the string into the cell; and then format the sections as you
desire.

Nick's routine gives an example of how to do that.
--ron

 

[Lunch]

In that case, instead of a formula, you will need to use VBA to concatenate the
string; insert the string into the cell; and then format the sections as you
desire.

Nick's routine gives an example of how to do that.
--ron

I'll take a look at it today and see if I can get it working. I don't
ahve a lot of VB experience so it's always an adventure.