Concatenate Text Formating
 

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

Concatenate Text Formating
 

You can't capture the formating when the result is the display of a
concatenation formula.
--
Gary''s Student - gsnu200717


"Lunch" wrote:

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

Concatenate Text Formating
 

On Apr 26, 6:10 pm, Gary''s Student
wrote:
You can't capture the formating when the result is the display of a
concatenation formula.
--
Gary''s Student - gsnu200717

"Lunch" wrote:
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???

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

Concatenate Text Formating
 

On 26 Apr 2007 16:01:04 -0700, Lunch wrote:

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

Concatenate Text Formating
 

On Apr 26, 8:05 pm, Ron Rosenfeld wrote:
On 26 Apr 2007 16:01:04 -0700, Lunch wrote:

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

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

Concatenate Text Formating
 

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

"Lunch" wrote in message
oups.com...
On Apr 26, 8:05 pm, Ron Rosenfeld wrote:
On 26 Apr 2007 16:01:04 -0700, Lunch wrote:

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

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

Concatenate Text Formating
 

On 26 Apr 2007 18:39:11 -0700, Lunch wrote:

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

Concatenate Text Formating
 

On Apr 27, 6:03 am, Ron Rosenfeld wrote:
On 26 Apr 2007 18:39:11 -0700, Lunch wrote:

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

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.