Format of a cell

I have a problem when I read the ":" colon as a separator from an excel or 
text file that uses it as a separator. 

the example I have is  that when the colon separator is followed by a 
number, say for example : 35, the number appears on the cell as 35:00:00 
which is a time format. 

It seems that Excel takes the separator as a time format for the cell and it 
does not allow change of the cell's format not even to a simple text format. 
Even when the formatting of the cell is  changed to a general format it 
generates a decimal number that is a multiplier of 24. If the value in the 
cell is ": 24" and we format the cell as text it would show a"1" in it.

Is there a way the maintain the cell as a text formatted cell where the data 
is maintained the same even if a colon is used as a separator?

 In other words if ": 24 " is entered the cell should stay as ": 24" after a 
text format is applied.

 
0
profG (1)
1/16/2006 3:13:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
366 Views

Similar Articles

[PageSpeed] 13

Either preformat the cell as Text, then type in the value.

Or start your entry with an apostrophe:   ': 24

If you're importing the file from a .txt file, then make sure you choose Text as
the format for that field.

If you're importing a file named *.csv, then rename it to *.txt and specify Text
for that field.

profG wrote:
> 
> I have a problem when I read the ":" colon as a separator from an excel or
> text file that uses it as a separator.
> 
> the example I have is  that when the colon separator is followed by a
> number, say for example : 35, the number appears on the cell as 35:00:00
> which is a time format.
> 
> It seems that Excel takes the separator as a time format for the cell and it
> does not allow change of the cell's format not even to a simple text format.
> Even when the formatting of the cell is  changed to a general format it
> generates a decimal number that is a multiplier of 24. If the value in the
> cell is ": 24" and we format the cell as text it would show a"1" in it.
> 
> Is there a way the maintain the cell as a text formatted cell where the data
> is maintained the same even if a colon is used as a separator?
> 
>  In other words if ": 24 " is entered the cell should stay as ": 24" after a
> text format is applied.
> 
> 

-- 

Dave Peterson
0
petersod (12005)
1/16/2006 3:02:16 PM
Reply:

Similar Artilces:

Combining multiple cells into a single cell
Is there a way to combine multiple cells in Excel 2000 into a single cell. This what I'm trying to do: combine data from column's a b c where column a contains 12345 colum b contains 78 column c contains 90 into a combined column that contains 1234567890 Thanks for any help on this matter. Hi Antonio! Use: =A1&B1&C1 But this returns the concatenation as text and not a number. If you want a number then use: =--(A1&B1&C1) The double negative is the same as multiplying by -1 twice and Excel will try (successfully) to evaluate the text as a number. Its the same ...

Shaded cells changing color for no reason
My setup: Dell Latitude D610 Laptop Windows XP Professional SP 1 Excel 2003 SP1 Problem: Spreadsheet A has some shaded cells (different shades of blue). If I open another spreadsheet, say B, within the same instance of Excel, and toggle back to the spreadsheet A, the cells in A have changed color all by themselves! This is driving me nuts! This only happens with certain spreadsheet Bs. Not all spreadsheets will cause the cells in spreadsheet A to change color. The two spreadsheets are completely independent, they do not link to each other. This is driving me crazy. If I print spread...

How can I footnote a particular cell in Excel?
Is there any way to attach a footnote to a cell? If I place it in the cell with a number my functions won't work. I caould use a comment but I really don't want to since it clutters the worksheet. I would like to use a simple footnote annotated in the cell so the note can be printed below the table or on a separate sheet. you could use custom format right mouse click on the cell, choose format cells - on the number tab choose custom you should see something like "general" in the little white line on the right hand side of the screen after this type "1" or ...

see the cells the formula relates to by double clicking the cell
this is probably a simple preference, but I cannot find it on my new Excel program. When I double click a cell with a formula, I am used to seeing it highlight the cells that are related to this formula. That does not happen with my new Exel. Can someone tell which preference to change? Thanks <Tools> <Options> <Edit> tab, And check or uncheck "Edit Directly In cell". -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! ---------------------------...

Changing the default cell size?
I'm looking to change the default size of each individual cell in Excel. Ideally, I would like to be able to resize each cell so that they're the size of four put together vertically. I realize I can select four cells at a time and merge them, but this is a hassle when its going to have to be done hundreds, if not thousands of times. Any and all help would be appreciated. Thanks in advance. -- Cheers, James Posted Via Usenet.com Premium Usenet Newsgroup Services ---------------------------------------------------------- ** SPEED ** RETENTION ** COMPLETION ** ANONYMIT...

Identify when Excel is displaying ### in a cell? (contents>width)
I am pulling data from an automated report. I'm trying to test for every possible condition, so that my VBA will continue to work for the rest of the year without additional tweaking. The target report is produced from an aspx web page (which has an "export to Excel" option). I do not have control over the source format. Some data in the exported Excel report is in merged cells. When numbers are increased (for example, to simulate the growth of YTD numbers) the display converts to "######" when the digits exceed the cell width for that font size. When ...

Determine next Saturday in calendar based on date in a cell
I need to determine the following Saturday based on a given date in a cell. For example, in one cell I have a date of 10/8/2006 - I need a function to return the date of 10/14/2006. Please don't post the same question to different workgroups in separate posts. Your question has been answered in another newsgroup. -- Kind regards, Niek Otten Microsoft MVP - Excel "Ann" <Ann@discussions.microsoft.com> wrote in message news:91343C82-106D-40AF-A919-A5E275ADBB57@microsoft.com... |I need to determine the following Saturday based on a given date in a cell. | For example, i...

Why does the blank cells blink black and white on my excel spread.
When I am in any spreadsheet in Excel the blank cells blink black and white. Excel on its own doesn't support blinking cells. If everything is ok with your video display/driver, then maybe the developer of the workbook thought that blinking cells would be useful. Try closing excel. then start it in safe mode. windows start button|run excel /safe file|open (your workbook) Do the cells still blink? If yes, I have no idea. If no, talk to the developer and ask him/her why and how to change it. sfulkrod wrote: > > When I am in any spreadsheet in Excel the blank cells blink black...

When using MONTH function on Blank Cell!! Returns Month=Jan!
When i use the MONTH(A3) Function and A3 is blank, excel takes it as a 0! ie. MONTH(0) and returns January as the month because the date in serial is 00-Jan-1900. How do i get around this! Here is my Formula: I am trying to count the number of months that are used, i have done this with this formula below: It works perfect for all months except for Jan! because it think that blanks cells are a serial date 0. {=SUM(IF(MONTH(DATA!A2:A7)=1,1,0))} __________________ | A | 1 | Date | 2 | 01-01-05 | 3 | 04-04-05 | 4 | 04-04-05 ...

Can excel take content from another cell and add information either side??
Hi all, If you can help me out you will save me literally weeks / months of work. Basically I am creating a products reviews website and the largest wholesaler of this market area has given me an excel spreadsheet of around 6,000 products, along with html description and product code. They have also given me (seperately) a directory of images. The images are named PRODUCTCODE-FULL.jpg (the same product code as is in the spreadsheet). I will be importing the whole file into my websites database and I REALLY need to get a column into the spreadsheet for images. So, the spreadsheet curren...

Format Referencing
How can I make it so not only the values in the cell but the formatting to that cell is referenced as well. Take for example I have a number "22" in a cell, but it is bolded and italicized as well as centered. Is there a way to copy these formats over as well? -- londar ------------------------------------------------------------------------ londar's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32970 View this thread: http://www.excelforum.com/showthread.php?threadid=560819 If you're using a formula (like =A1), then formulas don't work l...

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/ I know what you mean.......some cells seem to get &...

Format not holding in doc
I am building a new newsletter template from scratch and the default font is 6.9 in Comic Sans. I went in and created a new style and called it newsletter. When I draw a new text box, it comes up with the 6.9. I then have to scrolldown where my newsletter setting appears and everything is fine. I thought I did this correctly but I must have missed a step. USing PUB 2003 on XP Is this the tutorial you used? Setting Text Defaults using Normal.pub by Brian Kvalheim http://ed.mvps.org/Static.aspx?=Publisher/normal.pub -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps...

Add identical $ amount to group of cells
I have imported price lists (via OmniPage)into Excel. We would like to add a set dollar amount (our profit) to each of the cells. (ie. Add $500 to each cell). Can we do this globally? Thank you very much in advance, Eric Enter 500 into an empty cell, right click it, Copy Highlight all the cells you want to add 500 to, Right click them, Paste Special, Check Add, OK Delete the cell containing 500, Regards, "Eric W" <anonymous@discussions.microsoft.com> wrote in message news:16cf101c41cd5$ed9e41c0$a601280a@phx.gbl... > I have imported price lists (via OmniPage)into Excel...

Conditional Formatting
I want to add a column to a speadsheet that has been conditionally formatted, I want the column to be included in the rules. How do I do this? Copy a column that already has the rules you like, then paste - insert copied cells -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Mickey5" wrote: > I want to add a column to a speadsheet that has been conditionally formatted, > I want the column to be included in the rules. How do I do this? Copy the column in which you are having the Conditional Formatting and select t...

Add Sheet Cells into 1 sheet
People i have a excel spread sheet with many sheets inside it one for each day dating back 3 months sheets are named like 1st july05 till 1st oct05 1 for each day and blank sheets for weekends i want to add the totals of field b25 on all sheets onto a new sheet named totals can this be done? Mr. Banner, =SUM('First Sheet:Last Sheet'!B25) -- Earl Kiosterud www.smokeylake.com "Mrbanner" <mrbanner@swiftdsl.com.au> wrote in message news:1129255291.945224.222600@f14g2000cwb.googlegroups.com... > People i have a excel spread sheet with many sheets inside it one for ...

Conditional Format Stumper
I have a sheet of live stock quotes. Currently, when the high price and last price equal each other, I know the stock is at the high of the day, so I set the conditional format to change to bright green. What I would like to do is figure out how to change the high and last to bright green, ONLY when a new high is reached. So if the stocks trades multiple times at the high of the day, the color does not change. It would only change if a new high was made. Any suggestions would be appreciated. thanks! Mike Hi Mike would a CF formula such as =AND($C4=$B4,$C4=MAX($C$3:$C$100)) work? ...

conditional formatting problem 12-15-09
I have some cells which look like the following:- (they show three values from three other cells) either 1, 30, 5 or 1, *, 5 say where the "*" indicates a particularly large value Now, I am trying to highlight those cells which contain an "*". I am using SEARCH("*", A3) where A3 is the cell reference However, all my cells whether they include an "*" or not are highlighted What am I missing? Bob Generally SEARCH Function will treat the asterisk as Wild Card. Generally * is used in Search Function for declaring the cell can consist AN...

copy cell color but not conditional format equations
Following is highly abridged code from Bernie Deitrick regarding copying Type 2 conditional formats. Column C is formatted yellow if A1>B1, etc. There are 10 rows. I want to copy the cell color (but not the format equations) to column D. But the line bCheck = Application.Evaluate(c.FormatConditions.Item(k).Formula1) always returns Bcheck=True, then myRet is set to 1, and every cell in column D is colored (even where the condition is not true). I think I need another IF statement to check if the conditional format is true (and the cell is colored), but I can’t get anythi...

Locked cells
I would like to select all locked cells on a worksheet. How can I find all of the locked cells without having to loop through every individual cell on the worksheet? I know range.locked will return true if all cells in the range are locked but it will return false if one or more of the cells are unlocked. This makes it impossible to eliminate large areas of the workbook which may have unlocked cells without checking each individual cell. Thanks for any help Fred Hi Fred, I think that the only way you can do this is to loop through the cells. There is code at the foll...

Repeat code to "n" cells
Hi all My code reads one cell and through the options it locks or not others cells. Im trying to extend my programming to all subsequent cells. How can I do it? Like using "for" instruction?!?! Does anybody help me? Private Sub Worksheet_Change(ByVal Target As Range) If [E9]= "A" Then ActiveSheet.Unprotect ("") ActiveSheet.Range("G9:K9").Locked = True ActiveSheet.Range("F9").Locked = False [G9:K9].Interior.ColorIndex = 15 [F9].Interior.ColorIndex = 0 ActiveSheet.Protect ("") ElseIf Target.Cells.Text = "B" Then ActiveShee...

Hidden and Empty Cell Settings
I have blank cells in an Excel graph. I would like to treat them as "Gaps" instead of "Zero". However, this option (along with the "Connect data points with line" option) is grayed out in the Hidden and Empty Cell Settings box. ...

How do I format a row depending on the value of a cell in the row
I have a table set up and would like any row of the table where a particular cell in the same row is blank to be highlighted. In other words, for any cell in column J that is blank, I want the corresponding row to be highlighted. I'm trying the conditional formating 'Formula Is' but so far it's not working for me. Any ideas? Select the whole table, assume the table is A1:D10, so formula is should be =COUNTBLANK($A1:$D1)>0 replace A1 with the left uppermost cell in your table that you want to test for and D1 with the right uppermost cell, notice the mixed relative a...

Excel number format
People, here is the problem I am facing. my excel accepts both number in "100.00" (hundred) format and "100,00" (hundred ) format. both of them are numbers in my excel. But when I perform my calucations using both formats it doesn't calculate anything. I don't like the 100,00 format. how canI get rid of them? my excell automatically converts all the 100.00 numbers into 100,00 numbers. Please help Select the entire range and do right click and select format cells>Number>Category>Number and give ok. Still If you see the comma (,) in the cells t...

Linked Cell Zero Value
I have two Excel files with linked cells. When I enter data in file one, save and close it. I then open the file two. In the linked cells that have a null value in file one now show as a zero value in this second file. Can anyone tell me how to format the cells of file two so they have a null value, as the cells they are linked to in file one? Regards Nick One way for a cleaner look is to suppress zero values from showing in the sheet via: Tools > Options > View tab > Uncheck "Zero values" > OK -- Rgds Max xl 97 --- Please respond, in newsgroup xdemechanik <...