Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Very Simple formatting Question
I thought I understood most aspects of formatting numbers and text in Excel
until this came up... I have a spread sheet with numbers. I select the numbers and then right click to Format Cells and I select currency. The spread sheet seems to ignore the currency formatting and does not put in the dollar signs. What is worse than that is the column of numbers will not sort correctly. If I sort it gives me an incorrect sort order like: 119.42 12.62 120.38 122.43 Any idea what is going on? Quin |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Very Simple formatting Question
On Sat, 6 Dec 2008 13:41:01 -0800, Quin wrote:
I thought I understood most aspects of formatting numbers and text in Excel until this came up... I have a spread sheet with numbers. I select the numbers and then right click to Format Cells and I select currency. The spread sheet seems to ignore the currency formatting and does not put in the dollar signs. What is worse than that is the column of numbers will not sort correctly. If I sort it gives me an incorrect sort order like: 119.42 12.62 120.38 122.43 Any idea what is going on? Quin Your values were either 1. Entered as text 2. Entered in a column which had been pre-formatted as text 3. Imported from the web and have a trailing nbsp. To confirm, try this formula: =ISTEXT(cell_ref) where cell_ref is a cell containing one of these values that won't convert. To convert, first try: Select empty cell Edit/Copy Select column of values Edit/Paste Special/Add If that does not work, then enter this formula in an adjacent cell: =--TRIM(SUBSTITUTE(cell_ref,CHAR(160),"")) and fill down as far as required. Then Edit/Cut select original data (after making a backup) Edit/Paste Special/Values --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Very Simple formatting Question
Hi,
If your formatted as currency you have to re-enter the data to make that take effect. Select the cells in turn and tap F2 & enter and you should see your new format. the problem was probably cause by the cell being formatted as text before you enntered the numbers. Mike "Quin" wrote: I thought I understood most aspects of formatting numbers and text in Excel until this came up... I have a spread sheet with numbers. I select the numbers and then right click to Format Cells and I select currency. The spread sheet seems to ignore the currency formatting and does not put in the dollar signs. What is worse than that is the column of numbers will not sort correctly. If I sort it gives me an incorrect sort order like: 119.42 12.62 120.38 122.43 Any idea what is going on? Quin |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Very Simple formatting Question
Ron,
Your post was very helpful. Your guess was right about imported data from the web. It turns out that there were non-printing characters in the cells that prevented normal formatting and sorting behavior. I tried all your suggestions. IsText() function returns TRUE. So yes, even though I format as currency, Excel continues to treat the data as text. The Edit/PasteSpecial/Add suggestion did not work. The Trim function you suggested does work but I found another helpful procedure. When I Googled "Excel Non-Printing Characters" Here is that solution... START QUOTE FROM http://www.icehouse.net/jim_d/excel3.html TO REMOVE NON-PRINTING CHARACTERS: Select one of the problem cells. Press F2 to Edit the cell. Press End to move the blinking insertion point to the end of the cell contents. Hold down Shift and press the Left-arrow key once, to select/highlight the last character in the cell. Copy that character to the Clipboard. (You may then need to press Esc a couple of times to get out of Edit mode.) Select all the cells with the problem data in them. From the menus, do Edit, Replace; click in Find What and Paste in your invisible mystery character from the Clipboard. Leave Replace With blank. Click Replace All. END QUOTE The site quoted above has lots of information on this. I also used the LEN() function to discover the length of the data in the cells. In my case length was longer than the visible characters. I also used CODE(RIGHT()) to discover non-printing characters CHAR(160) to the right. Ultimately I repaired my sheet with find and replace as described in the procedure above. I also learned something about hidden characters. Thanks for your help, Quin |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Very Simple formatting Question
On Sun, 7 Dec 2008 07:29:01 -0800, Quin wrote:
Ron, Your post was very helpful. Your guess was right about imported data from the web. It turns out that there were non-printing characters in the cells that prevented normal formatting and sorting behavior. Glad you got it working. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple graph formatting question for dates. | Excel Discussion (Misc queries) | |||
Simple formatting question.... | Excel Discussion (Misc queries) | |||
Conditional formatting, simple question... | Excel Worksheet Functions | |||
Conditional formatting--simple question | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) |