simple yet nagging issue with functions and "format cells"

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

0
PIP211 (2)
11/29/2006 4:51:44 PM
excel 39879 articles. 2 followers. Follow

5 Replies
623 Views

Similar Articles

[PageSpeed] 55

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
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

<PIP211@gmail.com> wrote in message 
news:1164819104.530915.32300@l39g2000cwd.googlegroups.com...
> 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
> 


0
bliengme5824 (3040)
11/29/2006 4:59:32 PM
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.

-- 
Regards

Roger Govier


<PIP211@gmail.com> wrote in message 
news:1164819104.530915.32300@l39g2000cwd.googlegroups.com...
> 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
> 


0
roger5293 (1125)
11/29/2006 5:03:01 PM
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

Bernard Liengme wrote:
> 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
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> <PIP211@gmail.com> wrote in message
> news:1164819104.530915.32300@l39g2000cwd.googlegroups.com...
> > 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
> >

0
PIP211 (2)
11/29/2006 5:09:18 PM
Aaaah!!
Good thinking Bernard!

-- 
Regards

Roger Govier


"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message 
news:eb7b%23e9EHHA.3668@TK2MSFTNGP02.phx.gbl...
> 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
> -- 
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> <PIP211@gmail.com> wrote in message 
> news:1164819104.530915.32300@l39g2000cwd.googlegroups.com...
>> 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
>>
>
> 


0
roger5293 (1125)
11/29/2006 5:13:40 PM
Glad to have helped.
What use: for us geeks to debug the worksheets of others <g>
best wishes
-- 
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

<PIP211@gmail.com> wrote in message 
news:1164820158.619206.322260@14g2000cws.googlegroups.com...
> 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
>
> Bernard Liengme wrote:
>> 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
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> <PIP211@gmail.com> wrote in message
>> news:1164819104.530915.32300@l39g2000cwd.googlegroups.com...
>> > 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
>> >
> 


0
bliengme5824 (3040)
11/30/2006 3:27:16 PM
Reply:

Similar Artilces:

format cells not to except dashes
I am trying to format a range of cells not to except dashes (-) ie. when entering phone numbers 123-123-1234 Format is a display function only, it doesn't affect how entries are parsed (I assume you mean "accept"). You can use validation. Select your range (I'll assume that A1 is the active cell). Choose Data/Validation/Allow:Custom with the formula =ISERR(FIND("-",A1)) In article <6ADB6887-0F6C-4D23-A2D4-CCCFC69CD006@microsoft.com>, "kclover" <kclover@discussions.microsoft.com> wrote: > I am trying to format a range of cells...

how to format cells so that it only shows thousands.
howdy, i want to format cells so that number like 1,234,567 = 1,234 or 1235 how can i do this other than divide by 10. i just want to use the format number feature. thanks kev : -- Message posted from http://www.ExcelForum.com Hi try the custom format 0, or use 0,0, >-----Original Message----- >howdy, > > >i want to format cells so that > >number like > >1,234,567 = 1,234 or 1235 > > >how can i do this other than divide by 10. > >i just want to use the format number feature. > > >thanks > >kev :) > > >--- >Mess...

Format Cells, Text Alignment, Distributed
Greetings, I have a cell that I need to format so that it is justified but I need to control where the text breaks within the cell. For example, I have in cell A1 "Name: Jeff Willis Date: 6/16/2007". The cell width is fairly wide and I would like to format the cells Horizontal Text Alignment to Distributed. Is there a way to not make the cell distribute the text evenly? For example, can I embed a tab charactor or some other workaround between the 's' in Willis and the 'D' in Date to make span something like this Name: Jeff Willis Date: 6/16/20...

Format cells with a formula (7 conditions).
I want to format groups of cells with a different color according to the day of the week specified in one of the cells. Conditional formatting allows only 4 different conditions. Is there a way to use a formula to set the cell color? Put these Codes in sheet module and you can change colors as yo desire. Option Explicit Private Const xlCIBlack As Long = 1 Private Const xlCIWhite As Long = 2 Private Const xlCIRed As Long = 3 Private Const xlCIBrightGreen As Long = 4 Private Const xlCIBlue As Long = 5 Private Const xlCIYellow As Long = 6 Private Const xlCIPink As Long = 7 Private Const xlC...

format cells #6
I have solved this so it is just my curiosity at play here. Using someone else's sheet, I entered a formula (=D235/80) into a cell expecting a numeric result not realizing that the cell was formatted as Text (so I got to see the formula as text). So I changed the cell format to Number with 4 places. To my surprise, the cell still displayed the formula. I looked for a leading ' or space but there was none. Retyping the formula produced the result I expected in the first place. What is going on? TIA Len ______________________________________________________ Changing the forma...

Format Cells
I have a worksheet in which I want a column formatted so that I can put in a value that looks for like 10-11 or 2-3. If I use the Format Menu and choose Cells and specify General, as soon as I press enter, the value in the original cell changes to 11-Oct or 3-Feb and the format has changed to Custom d-mmm. I have tried selecting the column and choosing Edit-Clear-Formats but that doesn't help. I would appreciate any suggestions as to how to solve this problem. I don't think it existed when I first created the worksheet but has now appeared magically! Virginia Hi either for...

format cells option not working
when i highlight some cells and try to format them nothing happends, workbook and sheets are not protected, cells in question dot not have any data in them at all, totaly blank! if they are locked how can i findout? and how to unlock them? Thanks, Steve Maybe this? OFF: Changes to Fill Color and Fill Pattern Are Not Displayed http://support.microsoft.com/default.aspx?scid=kb;en-us;320531 -- Regards Ron de Bruin http://www.rondebruin.nl "Steven" <me@where.why> wrote in message news:K3Yfc.2505$UG1.744@newsfe5-gui.server.ntli.net... > when i highlight some cells and...

reformat cells
1. I have numbers in cells A1 through A6. I want to copy those numbers into cells B1 through G1. How do I reformat the cells so that they list horizontally. Note: I don't want to format the whole column. 2. How do I open two instances of excel on two different screens? #1. How about just selecting a1:A6 edit|copy select b1 edit|paste special|check Transpose. #2. I'd put another shortcut on the desktop that starts excel and click that or just: windows|start button|run type: excel (and hit enter) broken wrote: > > 1. I have numbers in cells A1 through A6. I want to copy ...

Compare fields and highlight the entries with Format Cells.
Seems like an easy task but don't know how. Can somebody help? I have to compare the entries of column A,B,C against that of column E,F,G. I did the EXACT function as follows: =EXACT(A1,E1) and so on. and filtered for FALSE. But I also want to automatically Fill Color for the cells where the entries don't match?? Is there ONE formula to do so? Thanx, Madan Select A1:C9999 (with A1 your activecell) Format|conditional formatting =exact(a1,e1)=false or =not(exact(a1,e1)) then do the same thing with E1:G9999 Mohan wrote: > > Seems like an easy task but don't know ...

Format Cells based on 1 or 2 Cell(s)
Hi, I have a spreadsheet with a combo box in the first two columns (selections are Create, Delete, and Modify). I would like to set the conditional formatting to change Cells A:AE green for Create, Red for Delete and Yellow for Modify for the particular row. I have tried the Conditional Formatting option, using the Formula Is option, with the formula ="Create" and =If ($A:$AE="Create) and neither works. I have been building a database all day and can't see straight, which could be part of the problem. Any one out there able to help a guy stuck in T.O.? Thanks Muc...

Where are "Custom Format Cells" stored ?
Hi, Do you know where are stored the data of "format cells" dialog box ? "custom" formats are not present in excel 2002, for some of my users... Thank you ! Yann First thing you do when you come back from lunch. Make a change to custom formats then run a file search for any file changed in the last few minutes/hour "saxo_saxo@yahoo.fr" wrote: > Hi, > > Do you know where are stored the data of "format cells" dialog box ? > "custom" formats are not present in excel 2002, for some of my users... > > Thank you ! > &g...

How do I format cells so that room numbers such as 1402, 1405a will sort correctly?
I'm trying to fix an Excel spreadsheet for a health care facility so that it can sort data by the first column, whci is the patient's room number. Room numbers can be pure numerical, or have a suffix at the end (a or b). So I have e.g. room numbers 1306, 2204, 1401b, 1613, 1402b. At the moment, the column sorts so that all the room numbers with a suffix follow all the numbers without a suffix. I've formatted the cells as General, Numbers, or Text, and get the same sort result each time. How can I fix this? HELP! All help gratefully received and acknowledged. -- Dan E...

too many different format cells
Dear Folks, There is something i have been met first time in my carrier, which is not big, and it is very strange. That it looks like.... I have had two workbooks and i create a third one by copying some sheets from each book. Yesterday i tried to copy another one sheet to my workbook, but the copied sheet lost its format and the program crashed. So when i reopen the excel the workbook worked quite good except the sheet which has lost its. format. When i tried to change the size of a word it shows me that message " Too many different format cells". and i couldn't made any differ...

How to format cells for uppercase entry
I need to format a column so that all cell force any text entry to uppercase. I've looked into the UPPER() function, but that is something that would actually take the value from some otther location and display it in uppercase where the fuction is located, which will not work. What I'm looking for is something similar to Format->Cells->Numbers, which allows you to define a format for numbers, so that however a number is entered in the cell it automatically displays it in the desired format. So I'd like to be able to have the data entry be in upper or lower case, but ...

How do I format cells in Excel to make 175/5 read 175?
I want to copy a list of numbers from a web site into a spreadsheet. The numbers all have a / in them, and this copies to the spreadsheet. Example 195/5. I would like to be able to format the cells so that the numbers will read 195 (losing the / and any digits after it. Using another column with a formula that would do this will also work. Thanks. Hi, Select the column and go to Data > Text to columns > Delimited. Select / as the delimiter -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "SJR4347" <SJR4347@discussions.mic...

how do I format cells to change date and time to just date
I want to format a column that contains date and time and I want it to show just the date and not the time. Going into format and clicking on the date and changing doesnt work. Probably the cells aren't really dates, but text. You can check with the ISTEXT() function. If they are text, formatting doesn't have any effect. -- Kind regards, Niek Otten Microsoft MVP - Excel "bondam" <bondam@discussions.microsoft.com> wrote in message news:F4D61EC1-30E2-4723-89E1-F47B545818EE@microsoft.com... >I want to format a column that contains date and time and I want it t...

Is it possible to format cells in cms
Does anyone know if it is possible to format cells in cms rather tha points. I am trying to size some cells to a specifice height fo printing. Can this be done or am I at the mercy of MS and have to jus guess the size. Any help would be much appreciated. Erdi -- erdi ----------------------------------------------------------------------- erdie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1551 View this thread: http://www.excelforum.com/showthread.php?threadid=27085 On Wed, 20 Oct 2004 10:44:31 -0500, erdie <erdie.1efmdc@excelforum-nospam.com> wrote: ...

Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit. Pat, Assuming the first date is in A1 B1: =A1+1 C1: =B 1+1 etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pat" <anonymous@discussions.microsoft.com> wrote in message news:115681B0-348E-447E-BB1F-F8347CFDB19B@microsoft.com... > Is there a way to format cells so that dates would change when the lead date is...

format cells #4
hi. how can i set by default the cell formatting to 'general' or 'number' or even 'text'? i mean i dont want excel to correct let's say: 1.5 to 01.may or 9/12 to 09.dec. is this possible? thanks. (i have some programs which export data to excel and instead of numbers in excel appear dates and i can't set the cell format before the data is exported) -- notrace2004 ------------------------------------------------------------------------ notrace2004's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34817 View this thread: http://www....

Format cells, category, postion
Is there anyway to make a number into a postion (e.g. add the st in 1st, or nd in 2nd), the difficult is that it needs to go on a cell with a formula in, so it can change from 1, 2, 3 etc. Thank you You can't do this as a formatting, but you could add a formula to another cell: with a number greater than zero in cell A1 to return the ordinal: =IF(A1>0,A1&IF(AND(A1>10,A1<20),"th",CHOOSE(MOD(A1,10)+1,"th","st","nd ","rd","th","th","th","th","th","th")),""...

Format Cells and a whole lot!
i have a sheet with only 1 column (A) each cell downward has an entry and it looks like this A 1 e_moneybagz1003@yahoo.com,"Ernest Vasquez" and so forth and so forth i need to remove the ,"Ernest Vasquez" from this cell and paste on a separate column (B) without the , and " " just the plain name. Why not just highlight cell A1 and manually delete the characters? Well because there are 25,213 entries from A1 to A25213. Gosh!!! -- djranks ------------------------------------------------------------------------ djranks's Profile: http://www.ex...

Format Cells?
Hello, Not sure if this is the easiest way to do this but I am wanting to create a series of cells A1 10.0.0 A2 10.0.1 A3 10.0.2 Etc. A14 10.0.15 A15 10.1.0 A16 10.1.1 A17 10.1.2 Etc. Is there an easy method of doing this perhaps with a Format function or will I have to create a sum to work out the last cell plus .0.1? Any help is appreciated. Andrew. Give this a try in any starting cell, eg in A1: =10&"."&INT((ROWS($1:1)-1)/16)&"."&MOD(ROWS($1:1)-1,16) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "@Homeonthe...

How format cells ?
Hi I want format money for example '12,35 z�' - i want to show this as '12.35 z�' How i may write fiormula to this work ? Fantom ps. Soory but my english is poor Fantom Format>Cells>Number>Custom #,##0.00"z then hit ALT + 0179(on numpad) then add another " Gord Dibben Excel MVP On Tue, 22 Nov 2005 15:30:51 +0100, "Fantom" <szczukot@skasujto.poczta.onet.pl> wrote: >Hi >I want format money >for example '12,35 z�' - i want to show this as '12.35 z�' >How i may write fiormula to this work ? > >Fantom >...

Like to format cells
I like to format the cell so that #DIV/0 wouldn't apper in the cell i parent data is not available yet. e.g. A1 = A2/A3 BI = B2/B3 C2 = C2/C3 and so on....... data for A2,A3,B2,B3 are available but data for C2,C3,D2,D3...... are NOT available yet. I want to see blank cell if C2,C3,D2,D3.....are not filled yet. Any help would be appreciated -- Message posted from http://www.ExcelForum.com in c1 enter =IF(ISERROR(C2/C3),0,C2/C3) "NYBoy >" <<NYBoy.14saas@excelforum-nospam.com> wrote in message news:NYBoy.14saas@excelforum-nospam.com... > I like to form...

"Format cells" not working
I have some Excel files in which "Format Cells" is not working. I canno bring up the format cell box either through Ctrl-1, the task bar, o right-clicking the mouse. While "Format Cells" works in other Exce files, as soon as anything is copied from one of the "infected" file to another file where "Format Cells" was working, "Format Cells" stop working in these other files as well. I am using Excel 2000 and Windows XP. Any ideas before I hurl my lapto from my 79th floor office -- Message posted from http://www.ExcelForum.com Hi Your sheet ...