Can't see formula result - only see formula - Excel 2002(OfficeXP)

  • Thread starter Thread starter LAArt
  • Start date Start date
L

LAArt

I have a problem that's been hounding me for the last few days. This is a
problem I have in various formulas, but here's a specific. I have a mail
list, and I'm trying to concatenate the ZIP and the +4 columns. I try
=AH1&"-"&BH1, and I try =concatenate(AH1,"-",BH1), all to no avail. The
cell simply displays the formula, and that's it. When I set a watch on the
formula cell, I see =AH1&"-"&BH1 in the Value column, and nothing in the
formula column. Both data columns are formatted as text, to avoid losing
leading zeros. Both columns, each cell has a little green flag in the upper
left corner. My calculation is set to Automatic.

What can I do? Please help!

Thanks.

Art
 
Try this: =CONCATENATE(A1&"-"&B1) using columns A and B.
HTh

Gilles Desjardins
 
<data columns are formatted as text>

That's the problem, Art.
Format as Number before you enter the formula. You can change the format
thereafter.

--
Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Your suggestion is to format the data column (which contains zip codes) as
number, and change it after - this will not work because of leading zeroes.
Some US Zip codes (especialy East Coast) begin with zeroes.
I found a temporary solution, by copying and pasting the whole worksheet
into a new workbook, but why is the problem happening in the first place, on
the original workbook?

Thanks for your replies.

Art
 
Art

Columns AH and BH are formatted as Text. OK.

The column where you have the formula is also formatted as Text. Not OK.

Format as General before inputting the formula.

Gord Dibben XL2002
 
This is a problem that had hounded me for a long time too
before someone finally told me this trick. If you do a
text to columns on that column using the fixed method, but
without actually splitting the column (just hitting next
through until finish), you should see your formula
results. This also works in cases where you've changed
the formatting, but it doesn't change what you're seeing,
and in this case, I think the reason is that there's
something hard-coded into the cell (often happens when
source data is downloaded) and this text to columns help
to "shake" that off. Hope this helps!
 
Back
Top