2 related re string formatting and function behavior.

  • Thread starter Thread starter TCEBob
  • Start date Start date
T

TCEBob

1. It's a puzzlement: I entered the function
=CONCATENATE(H1," ",H2)
and that's exactly what I got in the cell -- the literal function, including
"=".
The cell is formatted General.
Moreover, if I insert a mistake:
=CONCATENATE (H1," ",H2)
^space
The syntax checker pops up and when I allow it to fix the function
The newly fixed function is identical to the original one. And it works!

This seems to be true of all functions, not just strings. I tried =Sum(1,2)
with the same outcome

2. (Easier I hope) I wish the above concatenation to include line feeds for use
in word-wrap cells. Alt-Enter does it manually. Is there a symbol ("/n" maybe)
or can I type in the cr+lf ascii codes?

rs
 
Hi
1. check 'Tools - Options - View' and make sure 'Formulas' is NT
checked
2. Use
==CONCATENATE (H1,CHAR(10),H2)
and format the cell with word wrap.

Though I would not use cONCATENATE but
=H1&CHAR(10)&H2
 
1. Either you have View Formulas checked in Tools>Options>View or
the cells are pre-formatted as text.

Hit CRTL + `(above TAB key) to toggle Formula View on/off, if that is the
case.

If text cells, select the cells and Format as General then re-enter by
selecting a cell and hitting F2 then <ENTER>.

For a bunch of formulas a quick way to re-enter them is to select the cells
then Edit>Replace

what: =
with: =

Replace all.

2. =H1 & " " & CHAR(10) & H2

Note: wrap text must be enabled, otherwise you will see a square in the cell.

Gord Dibben Excel MVP
 
One guess for Q1 .. not really sure

Click Tools > Options > View tab
Is "Formulas" checked?
If so, uncheck it > OK

Another way to toggle formula view:
Press Ctrl + ~ (tilde key, just above tab key)
(perhaps you might have accidentally hit the above combo? said:
2. (Easier I hope) I wish the above concatenation to include line feeds for use
in word-wrap cells. Alt-Enter does it manually.
Click to expand...

"the above concat" is: =CONCATENATE(H1," ",H2)

Try in say, G1: =H1&CHAR(10)&H2
Format G1 with "wrap text"

If H1 contains "text1" and H2 contains "Text2"
then in G1 will appear:

Text1
Text2

Note: You can use "&" (much shorter to key in) to concatenate instead
 
Thanks all!

I did have the formula view off and switched a couple times with ctl-`. As I
said, the cells are formatted General. However, the spell seems to be broken and
all seems well. These are map, lot, owner, address, business name all stacked
and centered, ready for copying into Autocad.

As to formatting, I found that once the Function is typed in, changing the
format from Text to General does not help. You have to establish the format
first.

I guess CHAR(10) works better than CHAR(13).



rs
 
Back
Top