Concatenation in Excel 2003

[Guest]

I used to routinely use the concatenate formula in excel, with either syntax:
=CONCATENATE(A1,B1,C1) -OR-
=A1&B1&C1

Lately, sporadically, it just doesn't work, leaves my cell sitting with a
literal that looks like the formula rather than showing the concatenated
value. I don't have the Tool/Options/View Formula option checked, nor have I
used the Ctl-` command to turn this on or off. The only way I seem to be
able to get around this is by copying a concatenated formula option out of
help and pasting it, and then editing it to suit my situation, the Insert
Function does not work, either. I HAVE checked my formulas VERY CAREFULLY
for misplaced spaces or commas, etc., and I can't find any typos. Any
suggestions?

 

[Guest]

There are two potential reasons:

1. the cell has been accidentally formatted as Text. Reformat to General,
touch F2, touch ENTER

2. There may be a non-printing character before the = sign

 

[Dave Peterson]

Try formatting the cell as General (or anything but Text), then with that cell
selected, hit F2, then Enter.

 

[Guest]

I just tried doing this, as you suggested, and it DOES work, but it's a bit
peculiar. If the formula's sitting in a text-formatted cell and I reformat
to general, it doesn't change, but if I F2 edit the formula, and back up past
the = sign and then hit backspace, it will change (like I'm deleting what's
not there). Kind of seems like they're stuffing in an invisible ' symbol,
like they used to use for text left-justification. But in the old days, the
' left, " right, and ^ center used to be visible symbols if you looked at the
formula line, and they aren't visible here anymore.

I can't completely test this right now, since I'll have to wait until it
happens to me involuntarily again. But I think you've likely pegged it for
me. It surprises me, though, that I fixed it originally by pasting in text,
seems like that would have been more likely to be formatted as text rather
than general. So strange.

Anyway, thanks for your help! Much appreciated!

 

[Guest]

Dave,

Thanks for your reply. I just got finished answering "Gary's student" more
extensively (above), and I think you're both right. I don't know how I came
to have those cells formatted as text, unless there's some shortcut to do
that that I hit accidentally. I'll have to wait until it comes up again
sporadically to test if that's what's always happening, but I suspect you're
both right. Thanks again!

 

[Dave Peterson]

Saved from a previous post.

Excel likes to help.

Try this on a test worksheet.
Select A1 and hit ctrl-; (to put the date in the cell)
now select B1 and type: =a1

Notice that excel changed the format of B1 to match the format in A1.

Now format D1 as Text.
put ASDF in D1
put =D1 in E1
You see ASDF.

With E1 selected, hit the F2 key and then enter (to pretend that you're changing
the formula).

Excel has "helped" you by changing that cell's format to text.

I don't know of any way of changing this behavior.

I just select the cell, and reformat it to General (or whatever I wanted). I
hit F2 and then enter (to reenter that formula).

Sometimes this feature is nice, sometimes it ain't.

 

[Guest]

Dave,

I think you're right, this must be what was causing the original behavior.
And while I can't recall the exact scenario in which it happened to me
before, almost all the reports I do in excel import external data sources.
Quite often, when the imported fields are numerics, they were stored as alpha
in the original data source, especially when they're foreign keys in the RDB.
They import left justified with the little green warning triangle on the
cell corner. So it's quite likely that I came across this "helpfulness" from
the context, without actively reformatting anything myself.

Thanks for helping me convince myself I'm not crazy - and letting me enjoy
having my little propeller head spinner whizz for a bit. Always good to perk
up an afternoon! ;-D Linda

 

[Dave Peterson]

I may have convinced you that you're not crazy -- but just about this one
subject!!!!

<gd&r>
<grin, duck and run>

Dave,

I think you're right, this must be what was causing the original behavior.
And while I can't recall the exact scenario in which it happened to me
before, almost all the reports I do in excel import external data sources.
Quite often, when the imported fields are numerics, they were stored as alpha
in the original data source, especially when they're foreign keys in the RDB.
They import left justified with the little green warning triangle on the
cell corner. So it's quite likely that I came across this "helpfulness" from
the context, without actively reformatting anything myself.

Thanks for helping me convince myself I'm not crazy - and letting me enjoy
having my little propeller head spinner whizz for a bit. Always good to perk
up an afternoon! ;-D Linda