Formatting text in a CONCATENATEd sentence.

[Art MacNeil]

Hi all,

Is there any way to format certain portions of a concatenated sentence?
For instance, On Friday January 28th, 2005 I earned $ 100.00. Can I format
the date (say bold and blue) and also format the amount (say red if it is
below $ 200.00)?

Thanks,

Goob.

 

[Dave Peterson]

You can't do that character by character formatting with formulas.

If you convert it to values, then you can, though.

 

[Patrick BASTARD]

Hi, *Art MacNeil*
J'ai lu ton post VaCKd.197831$8l.186239@pd7tw1no
avec le plus grand intéret :

I tried this :

Sub Macro3()
Range("I3").Select
ActiveCell.FormulaR1C1 = "azertyuiop qsdfghjklm wxcvbn,;:!"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.ColorIndex = 3
End With
With ActiveCell.Characters(Start:=11, Length:=1).Font
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=12, Length:=10).Font
.ColorIndex = 50
End With
With ActiveCell.Characters(Start:=22, Length:=1).Font
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=23, Length:=10).Font
.FontStyle = "Gras"
.Size = 20
.ColorIndex = 5
End With

End Sub
May it help you.

Regards.

Patrick.

 

[Ken Wright]

Hi Patrick - that will only work with actual text in the cell, and not as a
concatenated formula - See Dave's post.

 

[Art MacNeil]

Damn, Damn, Damn!!!

Thanks for the info guys. You would think it would be easy enough for
MicroSoft to allow this but........not yet.

Thanks for your effort,

Art.

 

[Art MacNeil]

Wait?!!?, do you mean if I referred to a cell I could rather than just text,
I could do it?

 

[Dave Peterson]

Earlier versions of Lotus 123 used to allow you to embed formatting strings into
formulas. (I don't know if that's still true, though.)

But Excel never went that route.

 

[Harlan Grove]

Earlier versions of Lotus 123 used to allow you to embed formatting
strings into formulas. (I don't know if that's still true, though.)

But Excel never went that route.

....

I'm still a 123 user (as well as an Excel user). While 123 had 'compose
sequences' that allowed users to press two keys in sequence to generate char
codes between 128 and 255, I don't recall anything that would affect
formulas unless such extended chars were included in string constants inside
formulas.

If you mean embedding *PRINTER* formatting in formulas, it still exists if
you use Classic print commands (/P). Note that such cells would only contain
the printer formatting strings, and they'd appear as plain text in cells.

 

[Dave Peterson]

I'm pretty sure it was 123 v 2.4 (maybe after Always was more tightly integrated
into 123).

I could embed funny strings into values (and formulas) that would
bold/color/flash the next set of characters--until the turn off codes were
found. (Kind of like the HTML stuff.)

=if(a1="x","codetoboldcodetored"&b1&"codetounboldcodetounRed"&c1,"zzz")

This wasn't for printing, it was for displaying.

I know that I used to keep a magazine article (PCMag or PCWorld) on my desk to
make my life easier when I wanted to do this.

 

[sali]

I'm pretty sure it was 123 v 2.4 (maybe after Always was more tightly integrated
into 123).

I could embed funny strings into values (and formulas) that would
bold/color/flash the next set of characters--until the turn off codes were
found. (Kind of like the HTML stuff.)

=if(a1="x","codetoboldcodetored"&b1&"codetounboldcodetounRed"&c1,"zzz")

This wasn't for printing, it was for displaying.

well, you answered a question:
just use formula [macro in fact], that accepts mixed text and user defined
formatting sequences as parameters.
macro should read char-by-char and apply character formating [bold, color,
etc] as required on
selected cell

let be the formula:
=myformat({cmd1;"text1";cmd2;"text2";cmd3;"text3"})

with braces "{}" you may supply variable length array.
for example, "cmdx" is some integer code representing some format on/off.

 

[Dave Peterson]

Not in excel. excel doesn't support this.

I'm pretty sure it was 123 v 2.4 (maybe after Always was more tightly integrated
into 123).

I could embed funny strings into values (and formulas) that would
bold/color/flash the next set of characters--until the turn off codes were
found. (Kind of like the HTML stuff.)

=if(a1="x","codetoboldcodetored"&b1&"codetounboldcodetounRed"&c1,"zzz")

This wasn't for printing, it was for displaying.

well, you answered a question:
just use formula [macro in fact], that accepts mixed text and user defined
formatting sequences as parameters.
macro should read char-by-char and apply character formating [bold, color,
etc] as required on
selected cell

let be the formula:
=myformat({cmd1;"text1";cmd2;"text2";cmd3;"text3"})

with braces "{}" you may supply variable length array.
for example, "cmdx" is some integer code representing some format on/off.

 

[sali]

Not in excel. excel doesn't support this.

tested on xp/excel2002 [but quite sure works uppward from excel5]

you pass variable array consisting of pairs of cmd/text to sub, sub reads
all array elements, composes cell.characters.font property for each
character.

according to you, what part of this is not supported by excel?

I'm pretty sure it was 123 v 2.4 (maybe after Always was more tightly integrated
into 123).

I could embed funny strings into values (and formulas) that would
bold/color/flash the next set of characters--until the turn off codes were
found. (Kind of like the HTML stuff.)

=if(a1="x","codetoboldcodetored"&b1&"codetounboldcodetounRed"&c1,"zzz")

This wasn't for printing, it was for displaying.

well, you answered a question:
just use formula [macro in fact], that accepts mixed text and user defined
formatting sequences as parameters.
macro should read char-by-char and apply character formating [bold, color,
etc] as required on
selected cell

let be the formula:
=myformat({cmd1;"text1";cmd2;"text2";cmd3;"text3"})

with braces "{}" you may supply variable length array.
for example, "cmdx" is some integer code representing some format

on/off.

 

[Dave Peterson]

The part that actually changes the format of portions of the string within the
cell.

Can you post how you had text1 come out bold, blue
text2 come out red, italics
text3 come out in Wingdings

And still keep that formula in the cell?

Not in excel. excel doesn't support this.

tested on xp/excel2002 [but quite sure works uppward from excel5]

you pass variable array consisting of pairs of cmd/text to sub, sub reads
all array elements, composes cell.characters.font property for each
character.

according to you, what part of this is not supported by excel?

I'm pretty sure it was 123 v 2.4 (maybe after Always was more tightly
integrated
into 123).

I could embed funny strings into values (and formulas) that would
bold/color/flash the next set of characters--until the turn off codes were
found. (Kind of like the HTML stuff.)

=if(a1="x","codetoboldcodetored"&b1&"codetounboldcodetounRed"&c1,"zzz")

This wasn't for printing, it was for displaying.

well, you answered a question:
just use formula [macro in fact], that accepts mixed text and user defined
formatting sequences as parameters.
macro should read char-by-char and apply character formating [bold, color,
etc] as required on
selected cell

let be the formula:
=myformat({cmd1;"text1";cmd2;"text2";cmd3;"text3"})

with braces "{}" you may supply variable length array.
for example, "cmdx" is some integer code representing some format

on/off.

 

[sali]

The part that actually changes the format of portions of the string within the
cell.

Can you post how you had text1 come out bold, blue
text2 come out red, italics
text3 come out in Wingdings

And still keep that formula in the cell?

unfortunately, not!
example works for *sub*, but *function* obviously may not write to any cell,
just read [and return value to active cell, of course].

i overlooked that restriction. sorry.

Not in excel. excel doesn't support this.

tested on xp/excel2002 [but quite sure works uppward from excel5]

you pass variable array consisting of pairs of cmd/text to sub, sub reads
all array elements, composes cell.characters.font property for each
character.

according to you, what part of this is not supported by excel?

sali wrote:

I'm pretty sure it was 123 v 2.4 (maybe after Always was more tightly
integrated
into 123).

I could embed funny strings into values (and formulas) that would
bold/color/flash the next set of characters--until the turn off

codes
were

found. (Kind of like the HTML stuff.)

=if(a1="x","codetoboldcodetored"&b1&"codetounboldcodetounRed"&c1,"zzz")

This wasn't for printing, it was for displaying.

well, you answered a question:
just use formula [macro in fact], that accepts mixed text and user defined
formatting sequences as parameters.
macro should read char-by-char and apply character formating [bold, color,
etc] as required on
selected cell

let be the formula:
=myformat({cmd1;"text1";cmd2;"text2";cmd3;"text3"})

with braces "{}" you may supply variable length array.
for example, "cmdx" is some integer code representing some format on/off.

 

[Dave Peterson]

i overlooked that restriction. sorry.

No need. Welcome to my club <vbg>.

The part that actually changes the format of portions of the string within the
cell.

Can you post how you had text1 come out bold, blue
text2 come out red, italics
text3 come out in Wingdings

And still keep that formula in the cell?

unfortunately, not!
example works for *sub*, but *function* obviously may not write to any cell,
just read [and return value to active cell, of course].

i overlooked that restriction. sorry.

Not in excel. excel doesn't support this.


tested on xp/excel2002 [but quite sure works uppward from excel5]

you pass variable array consisting of pairs of cmd/text to sub, sub reads
all array elements, composes cell.characters.font property for each
character.

according to you, what part of this is not supported by excel?

sali wrote:

I'm pretty sure it was 123 v 2.4 (maybe after Always was more tightly
integrated
into 123).

I could embed funny strings into values (and formulas) that would
bold/color/flash the next set of characters--until the turn off codes
were
found. (Kind of like the HTML stuff.)


=if(a1="x","codetoboldcodetored"&b1&"codetounboldcodetounRed"&c1,"zzz")

This wasn't for printing, it was for displaying.

well, you answered a question:
just use formula [macro in fact], that accepts mixed text and user
defined
formatting sequences as parameters.
macro should read char-by-char and apply character formating [bold,
color,
etc] as required on
selected cell

let be the formula:
=myformat({cmd1;"text1";cmd2;"text2";cmd3;"text3"})

with braces "{}" you may supply variable length array.
for example, "cmdx" is some integer code representing some format
on/off.