Cell Not Formatting correctly.

Okay, here is my issue.  I formatted a column for phone number - the
only way the phone number will show formatted is if I double click in
the cell or if I click on the cell and press F2. 

What's going on?  I tried to Edit|Clear|Formats but that didn't work
either.  

Any suggestions?  The list I need to format is over a few thousand
rows.

Thanks for the help!



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/21/2003 6:44:58 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
576 Views

Similar Articles

[PageSpeed] 56

I know what you mean.......some cells seem to get "stuck" in the formatting
cycle.......what I would do is to just left-click on some clean un-formatted
cell, then left click on the little formatting paintbursh up in the tool
bar, then left click on the cell in question......sometimes this will "break
the loop" and then you can just re-format it to whatever you want, and go on
from there.......

Vaya con Dios,
Chuck, CABGx3



"jrdavis" <jrdavis.vnx4z@excelforum-nospam.com> wrote in message
news:jrdavis.vnx4z@excelforum-nospam.com...
> Okay, here is my issue.  I formatted a column for phone number - the
> only way the phone number will show formatted is if I double click in
> the cell or if I click on the cell and press F2.
>
> What's going on?  I tried to Edit|Clear|Formats but that didn't work
> either.
>
> Any suggestions?  The list I need to format is over a few thousand
> rows.
>
> Thanks for the help!
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/
>


0
croberts (1377)
10/21/2003 11:23:32 PM
If the cell(s) were formatted as text originally, then changing the cell to
phone number won't help.  Hitting F2, then enter tells excel you're reentering
the data, so it sees it as numeric and then it can format it as a phone number.

Another way to "reenter" the data is to make a mass change.

If you can pick out a digit that's used a lot (maybe 1) and just 
edit|replace 1 with 1 (2 with 2, etc)

excel will respect the phone number format.

(If you're lucky, you won't need a lot of mass changes!)

jrdavis wrote:
> 
> Okay, here is my issue.  I formatted a column for phone number - the
> only way the phone number will show formatted is if I double click in
> the cell or if I click on the cell and press F2.
> 
> What's going on?  I tried to Edit|Clear|Formats but that didn't work
> either.
> 
> Any suggestions?  The list I need to format is over a few thousand
> rows.
> 
> Thanks for the help!
> 
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/22/2003 3:01:32 AM
Reply:

Similar Artilces:

Formatting Formulas
I have a series of formulas that creates English Language labels out of a variety of VLOOKUP's and the ever helpful & sign. Basically, I used the VLOOKUPS to get "Big", "Bag", "Delicious", and "Apples" in cells A1-D1, respectively. Then, in E1, I've got =A1&" "&B1&" of "&C1&" "&D1 so that it comes out Big Bag of Delicious Apples In E2 it says Small Bundle of Tart Grapes and E3 says Big Bundle of Tart Apples, etc. What I'd like to have, though, is some sort of formatting set u...

delete row if 2 cells are empty
Hello I get a daily report and I have to delete the entire row if cell AA and Cell AB are empty. staring from row 4 till the end. I have been doing this manually but is there a VBA code that would make this faster. thanks in advance Unless aa a/o bb populated with a space bar this should do it. Sub delrowsifaaempty() lr = Cells.find(What:="*", After:=[A1], _ SearchDirection:=xlPrevious).Row For i = lr To 4 Step -1 If Cells(i, "aa") = "" And Cells(i, "ab") = "" Then Rows(i).Delete Next i End Sub -- Don Guillett ...

Correcting a typo in large value list
I'm new to Project so this may be simple. I have several project files that the value list for a Text column was incorrectly entered. Is there a way I can add a string prefix to all 200+ values in the list? For example, the data I have in the Text13 column is "1.3.1" and I want "ABC.1.3.1". I've tried to do a formula but I keep getting a recursive error since I need the new value to be in Text13 plus I need to keep the ability to be able to select a new value from the list (in case something changes). If it was Excel, this would be simple becaus...

Too many different cell format #3
Dear All I have a quite big excel and i am trying to make some changes in format and i receive the message "Too many different cell format" Probably i have many different cell formats. From where i can set up the format in order to know what must be the format of the excel file? I need away to identify the format from all sheets in order to give to the all sheets as common format as i can. Any ideas? Some info: XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=213904 I'd remove some of the formatting. (maybe format per colu...

Changing the number format
Hi everyone, I am using Excel 2000. I am running Excel with the regional setting set to Germany. So, Excel interprets "," as a decimal point. What want to do is make one particular workbook interpret "." as the decima point. However, I cannot change the regional settings on my PC. Is there any way to tell Excel 2000 to interpret "." as the decima point. I went to "Format", "Cells", "Number". However, I cannot chang the "," to a ".". I hope I am clear enough. Please let me know if I need to clarify a bi more. T...

Select Cells Macro
I don't like to take my hands off the keyboard, even to reach for the cursor pad. So I have four macros: Up, Down, Right, and Left assigned to Ctrl-k, Ctrl-i, Ctrl-l, and Ctrl-j. I'd like to be able to select cells the same way, the equivalent of Shift-Up, Shift-Down, Shift-Right, and Shift-Left. When I record a macro to do Shift-Right for example, and assign it to Ctrl-Shift-l, all it will do is select one cell to the right plus the initial cell. I want it to continue to select to the right if I press Ctrl-Shift-l again: Sub SelRight() ' ' SelRight Macro '...

"Too many different cell formats" is preventing file from opening
An Excel 2000 workbook saved successfully but when attempting to open it again I receive the message, "Too many different cell formats." I have since learned that a maximum of 4000 format combinations are allowed in a workbook. I've also learned how to correct the problem. Unfortunately, Microsoft's instructions don't address how to get the file to open in order to carry out the corrective actions necessary. Any ideas about getting the file open? A recent post from Dave Peterson on this subject.......... Saved from a previous post: XL: Error Message: Too Ma...

Copying data from cells A1,B1,C1 on tab1 to tab2
I want to copy data from cells A, B and C on Tab 1 of my workbook to other tabs in my workbook dependant on which option is selected from a dropdown list in cell D of Tab1 Example: I have 'company name' in cell A, 'Account Number' in Cell B and 'Sales' in cell C on tab 1, I then select 'Blue' from a dropdown list in cell D on tab 1. I then want excel to copy the data in cells A, B and C, on tab 1 into another tab in the same workbook named 'Blue'. can anybody help please???? -- Thanks, Dan Select the sheet tab which you want to wor...

Format #3
I am trying to take a column on a excel spreadsheet and transfer it to notepad or a general text without it changing all format. How do I do this? What happens when you select the column, edit|copy and paste into NotePad? lbierer wrote: > > I am trying to take a column on a excel spreadsheet and transfer it to > notepad or a general text without it changing all format. How do I do this? -- Dave Peterson It takes everything with it, the cells, etc and changes the format so I have words all over the page. Does this make sense? "Dave Peterson" wrote: > What happ...

How to count merged cells
Can someone help me in developing a formula that will: 1. Count how many merged cells in a column that are created from 2 cells AND contain both text and numbers? 2. Count how many merged cells in a column that are created from 3 cells AND contain both text and numbers? 3. Council how may merged cells in a column that are blank. If you look in the archives of this group you will see countless examples of reasons not to use merged cells. To concatenate cells is fine, to merge is asking for trouble. -- David Biddulph "Excel Ella" <ExcelElla@discussions.microsof...

Cell #2
I want to protect a cell after entering formula. But at the same time the entire worksheet except that cell should be unprotected/Open for edit. Kindly suggest solution for the same. Hi, It's easy. Select all the cells you want to allow access, Ctl-click on the cell you wish to protect. Go to menu, format>cells choose Protection, uncheck "locked". and OK. you're done. jeff >-----Original Message----- >I want to protect a cell after entering formula. But at the same time the entire worksheet except that cell should be unprotected/Open for edit. > >Ki...

Error in AVERAGE(IF...) formula; involves Zero Value and Blank Cells
Hi there, I am getting an error in this formula: =AVERAGE(IF(($Y13:AH13<>0)*($Y$10:AH$10<>0),$Y13:AH13/$Y$10:AH $10))*100 Excel states that a value used in the formula is of the wrong data type, and the calculation steps underlines the first ($Y13:AH13 and results in #Value!<>0. The long-hand way of typing it is (up to column AG): =(((Y13/Y$10)+(Z13/Z$10)+(AA13/AA$10)+(AB13/AB$10)+(AC13/AC$10)+(AD13/ AD$10)+(AE13/AE$10)+(AF13/AF$10)+(AG13/AG$10))*100)/ COUNT(Y13,Z13,AA13,AB13,AC13,AD13,AE13,AF13,AG13) The data values, which are student scores on assignments, are currently ...

Have excel export cell format other than text; need time and date not number value
First thank you for taking the time to read about my problem. Secondly I'm not an xml or excel hotshot. I can barely write a macro. My problem is this. When exporting my xml data, the time/date values lose their readable format. A time of 01:30 am, for example, becomes something like 0.001353 for example. What do I need to do to keep the same format as is in Excel? It would sure be great if you could help. Kind regards ...

Formatting phone numbers
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi all, <br><br>I am in the middle of a project and I was hoping someone could help me. <br><br>I am merging mailing lists which contain phone numbers. Some of the phone numbers are formatted (800) 000-0000 and some are formatted 800-000-0000. I have to choose one of the formats. <br><br>Is there a way to change the formats on all instead of doing it manually? <br><br>Any help would be appreciated. <br><br>Thank you, Bonnie highlight the column where the ...

best formula for finding a cell value?
Hello, I have created a matrix which has three columns: "north", "south" and "west". The rows below the columns are sequenced by their respective row number and below each column has different values. My questions is: If I create three cells- "Direction","Row" and "Value", how can I populate the intersecting value in "Value" by when I input in "Direction" and "Row"?? Example: Direction = north; Row = 2; Value = [would this be a DGET or IF function?] Thanks in advance for your responses!! -- ...

Conditional Datapoint Formatting
I'm graphing time series data values in weekly buckets. I'd like to d 2 things that involve conditional formatting of a specific datapoin within the series. -I'd like the datapoint of the most recent week to have a differen symbol/color -I'd like the highest/lowest datapoints in the series to have different symbol/color Is this possible & how -- Message posted from http://www.ExcelForum.com Stefan - I show a couple techniques you might find useful: http://peltiertech.com/Excel/Charts/ConditionalChart1.html http://peltiertech.com/Excel/Charts/FormatMinMax.ht...

Conditional Formatting based on multiple criteria
I want to conditionally format blank cells in column B to blue when the value in corresponding cells in column A contain the word "Custom". However, once I place a value in a blank highlighted cell in column B I then want the formatting removed. Example: Row 2 - - Cell A = "Custom" - - Cell B is blank - - therefore cell B2 turns blue Then Row 2 - - I enter a value in cell B2 - - the blue formatting goes away. Use a formula of =AND(B2="",A2="Custom") -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) <twlo...

Inserting a new column affects conditional formatting
Hey, I have got conditional formatting setup on a sheet that highlights a row based on the value in two columns (K and L). The formula for this is; =AND(INDIRECT("L"&ROW())="No", INDIRECT("K"&ROW())="Sent") I have got 6 varieties of this, and it all works well and highlights each row correctly. However, I need to insert a new column before K - and when I do I lose the conditional formatting. I have tried manually altering the formula as; =AND(INDIRECT("M"&ROW())="No", INDIRECT("L"&ROW())="Sent&quo...

Excel: with an if condition display a row of cell if true
Hello, I am a relatively new user and I was asked to set up a contact list for my office. I would like to have one master list on the first worksheet and then kind of query different sections into new worksheets. Is this possible? For example in the main worksheet i would have name, region and supervisor. In the second worksheet I would want only Peel Region enteries to be displayed. The main goal is: If a change something on the first worksheet I would like the other worksheets altered automatically. PLEASE HELP ME. Copy the data that you want from the main worksheet and then paste...

Ranking a column with some cells having #value!
I have a spreadsheet that has 20 rows. Depending on the input, not all rows will be entered with data. Those without data will have #value!. Problem is how can I formulate the ranking so that I can always see the ranking of the 20 rows and the ranking will auto exclude those rows having #value!? Thank you. Why don’t you post your formula which is resulting #value!. Otherwise try this. =IF(ISERROR(Your Formula),0,Your Formula) Which will get you the result of 0 instead of #value!. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) ---...

What would the formula be for counting highlighted cells in Excel.
Does anyone know how you can count the number of cells that are highlighted Try something like the following: Dim CellCount As Long CellCount = Selection.Cells.Count -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "chipsters" <chipsters@discussions.microsoft.com> wrote in message news:CE944230-4A70-4E3F-A2DE-127202199D00@microsoft.com... > Does anyone know how you can count the number of cells that are > highlighted If by hightlighted means selected Sub countselectedcells() ms = 0 For Each c In Selection m...

How do I send a publisher document in HTML format
We are attempting to provide a brochure to our publicist in the requested HTML format so she can use the brochure we created in an email camoaign. Hi Robert Mitchell (Robert Mitchell@discussions.microsoft.com), in the newsgroups you posted: || We are attempting to provide a brochure to our publicist in the || requested HTML format so she can use the brochure we created in an || email camoaign. Which version of Publisher are you using? -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights...

Custom formatting to force a decimal place
Oh wise ones, I have some custom cell formatting that prefixes some text to a value. I would like to force 1 decimal place even if it is zero. Currently if it is zero it just displays the whole number. Any suggestions? custom format is "LFE Scan="###.# "LBS" Thanks, Mike Try: "LFE Scan="0.0 "LBS" -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Mike K" <MikeK@discussions.microsoft.com> wrote in message news:8E0D35E4-4023-4A10-AB8D-5599C923C...

How can we find out different format combination in a worksheet?
Excel support 4000 different format combination in a excel sheet then Is there any way to find out different format combincation in a worksheet. ...

How do I change the default cell format?
I always have to change the cell format (Alignment, wrap text, etc) because I don't like the default settings. Could anyone tell me where can I change these setttings? Thanks! Álvaro You can create a new workbook and save it as book.xlt in your XLStart folder. For each sheet in that workbook, format the sheets the way you want. When you click on the New icon on the standard toolbar, your new workbook will inherit all the settings from this template workbook. You can also create a new workbook and save it as sheet.xlt (also saved to your XLStart folder). Then any worksheet yo...