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

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

On Apr 26, 6:10 pm, Gary''s Student
<GarysStud...@discussions.microsoft.com> 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

On 26 Apr 2007 16:01:04 -0700, Lunch <(E-Mail Removed)> 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

On Apr 26, 8:05 pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On 26 Apr 2007 16:01:04 -0700, Lunch <cjohn...@gmail.com> 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Apr 26, 8:05 pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> > On 26 Apr 2007 16:01:04 -0700, Lunch <cjohn...@gmail.com> 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
>
>
>

On 26 Apr 2007 18:39:11 -0700, Lunch <(E-Mail Removed)> 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

On Apr 27, 6:03 am, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On 26 Apr 2007 18:39:11 -0700, Lunch <cjohn...@gmail.com> 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.