Simple MS Excel $ formatting question 1

[mohebk]

Hi (MS Excel 2003).

This happens to me once in a while and not sure why. I copy some information in a spreadsheet and try to format the column to a currency format. I hit the $ button from the top but it does not show the $ sign or correct decimal points. I go to format cell and choose currency and still do not do it. Not sure how else can I make sure the column gets formatted.

Thanks


Mo

 

[PHV]

Are you sure the values are numeric ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mohebk]

Yes, they are numbers. I put the cursor in the cell and hit F2 to edit and leave out of it and it works. I am not sure whey do I need to edit it to take affect.

Mo

 

[AnotherHiggins]

This is a common problem with imported data. The cause is that the data is formatted as text. Even though you might have tried to change the formatting, even though you can check the formatting of the cells and Excel says they are formatted as number, they are really still formatted as text.

Following are two solutions. Either will coerce all cells in the column without you having to 'tickle' every individual cell:

[tab]Text to Columns
[ul][li]Select the column containing the offending data[/li]
[li]Go to Data > Text to Columns[/li]
[li]Choose either Fixed Width or Delimited[/li]
[li]Press Next[/li]
[ul][li]If you selected Delimited, uncheck all options[/li]
[li]If you selected Fixed Width, double click on any column breaks Excel tries to put in[/li]
[ul][li]Either way, the important thing here is to ensure that you do not see any column breaks in the Data Preview[/li][/ul][/ul]
[li]Press Next[/li]
[li]In the top right corner, select General[/li]
[li]Press Finish[/li]
[/ul]

[tab]Times One Fix
[ul][li]In a cell to the right of all the imported data, type [blue]1[/blue][/li]
[ul][li](this should be right-aligned, indicating that the cell is formatted as a number)[/li][/ul]
[li]copy that cell[/li]
[li]select the cells that are giving you trouble[sup][red]*[/red][/sup][/li]
[li]Go to Edit > Paste Special > Multiply[/li]
[li]Now try applying a new format to those cells [/li][/ul]

[sup][red]*[/red][/sup] Note that any null cells that you select will be converted to zeros. For this reason I recommend that you don’t select an entire row/column.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

I just noticed that this question is in the VBA forum - so I guess I should provide a VBA solution.

Range("A:A").value = Range("A:A").value

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mohebk]

I like this solution a lot:

In a cell to the right of all the imported data, type 1
(this should be right-aligned, indicating that the cell is formatted as a number)
copy that cell
select the cells that are giving you trouble*
Go to Edit > Paste Special > Multiply
Now try applying a new format to those cells


Because I copy the data from MS Access and paste it into MS Excell and don't get to go through the import wizard.

Thanks again.



Mo