simple yet nagging issue with functions and "format cells"

[PIP211]

i'm sure this is a really simple issue...my knowledge of excel is not
comprehensive, it's all self-taught and very specific (i.e. i have a
great deal of knowledge on some stuff and other stuff i've never used).

i have a spreadsheet with five worksheets in it, and it's not letting
me type in any functions or alter the formatting of cells. essentially
the sticking point is there is a column of zip codes that has lost its
leading zero, and i'm trying to either concatenate it with a leading
zero or change the box format to zip code. no luck on either. if i type
in any formula, it just displays it in the box like regular text.

this spreadsheet has a few macros in it. is that what is causing the
issue? i've never worked with macros before, so my knowledge there is
essentially non-existent.

thanks for any and all help...
micah

 

[Bernard Liengme]

For a 5-digit zipcode, format the cells with Custom format: 00000

Formulas become text? Sounds like display formula is ON. Toggle off with
CTRL+` (that the key to the left of 1 on the top row of the 'typewriter'
keys - just under ESC generally)

best wishes

 

[Roger Govier]

Hi Micah

It may be that the column where you are trying to enter the codes has
been preformatted as Text.
Click the column heading, Format>Cells>Number>General
then re-enter ="0"&A1 or whatever you were using to concatenate the
leading 0 back to the number.

 

[PIP211]

haha, that's it! the CTRL+` works. man, i never would have guessed that
one - not least of all because i've never heard of it, it just seems
like a hopelessly obscure feature. what would you use that for,
anyways?

now i can format the cells, too, which i couldn't do before.

thanks bernard - i owe you a beer (or drink of your choice).

micah

 

[Roger Govier]

Aaaah!!
Good thinking Bernard!

 

[Bernard Liengme]

Glad to have helped.
What use: for us geeks to debug the worksheets of others <g>
best wishes