HOW TO: In a formula, how to change the format of the resulting te

[Guest]

Hi TWIMC

I need to how to change a proportion of text in a resulting concatenation
formula.

cell A1 contains the text in bold "ABCDEF", cell A2 containes "GHIJKLM" cell
A3 contains =A1 & " " & A2 but I want the cell A1 part to remain in bold. Is
there a way of using the TEXT function e.g. =TEXT(A1,BOLD) & " " & A2?

TIA
KM

 

[Peo Sjoblom]

Are you saying that the text changes to lower case when you use the formula?
egardless to have upper case you can use

=UPPER(A1)" "&A2

 

[Guest]

NO, I'm saying that the first part is in bold, BOLD means, well bold text,
like Italic or Underlined ..... definitely not UPPER case that you thought.

Anyone else have any ideas or do I have to re post?

regards
KM

 

[Peo Sjoblom]

Doh! Sorry about that, no there is no built in function that will do that

 

[Guest]

So how come if you type an load of text into a cell, select half of it, click
on the Bold button on the menu bar, press enter and half of the cell contents
are in bold but the othe half are not. How does Excel do it?

Any ideas, much appriciated.

ciao
KM

 

[Dave Peterson]

But it won't work with a formula even if you do it manually.

 

[Gord Dibben]

Typo Patrol<g>

Try =UPPER(A1)&" "&A2

Kevin

There is no BOLD or FONTCOLOR function.

You can use the Case function as Peo has shown.

To get the Bold part you can copy>paste special>values then Bold ABCDEF
manually.


Gord Dibben Excel MVP

 

[ron]

Hi TWIMC

I need to how to change a proportion of text in a resulting concatenation
formula.

cell A1 contains the text in bold "ABCDEF", cell A2 containes "GHIJKLM" cell
A3 contains =A1 & " " & A2 but I want the cell A1 part to remain in bold. Is
there a way of using the TEXT function e.g. =TEXT(A1,BOLD) & " " & A2?

TIA
KM

You can do this with a VBA sub, but it will require having the sub
remove the formula and replace it with just the text string. There are
ways to make this volatile, also. But you would have to provide more
precise specifications.

If interested, post back & I'll post some code when I get back home
tomorrow or Monday.

~ron

 

[Harlan Grove]

So how come if you type an load of text into a cell, select half of it,
click
on the Bold button on the menu bar, press enter and half of the cell
contents
are in bold but the othe half are not. How does Excel do it?

....

No one is saying there's a good reason you can't do this in formulas, though
it'd require running the formatting engine after the recalculation engine if
it were possible.

In a nut shell, Excel provides access to the rich text Characters object for
cells containing literals, but it doesn't provide access to this class for
cells containing formulas. It's a design decision/limitation. Nothing can be
done to change it until Microsoft decides to reprogram it.

 

[Guest]

It is funny though, because in the mid 90's Lotus-123 was able to have
different formats in formulas (e.g. Bold, Italic, underline and font colors
etc.). Strange that Microsoft have chosen that limitation.


"Harlan Grove" skrev: