Formatting to Currency does not get applied

[Guest]

I have a column of numbers formatted without dollar signs
& decimal points:

1234
5678
9100

I want to apply Currency formatting with decimal points.
I select the cells, apply the formatting but it doesn't
change how the cells are displayed.

There's no conditional formatting on the cells - I can't
figure out why it isn't allowing me to change the cell
format.

Any ideas?

 

[Andy B]

Hi

When you select Currency from the Format Cells list, does it show a symbol
on the right hand side of the dialog box?

 

[Guest]

Yes, it shows the $ in the "Symbol" field but in the
sample at the top of the dialog box it doesn't have the $
applied either.

 

[Gord Dibben]

My guess is that the "numbers" are actually text.

Copy an empty cell. Select your range of numbers and Paste
Special>Add>OK>Esc.

Format as Currency.

Gord Dibben Excel MVP

 

[Guest]

Ok - I've discovered the problem. There is a blank space
in each of the cells after the amount:

1234 _

If I click on the individual cells and backspace out the
blank character, it will change the formatting to
currency.

Isn't there a way I can remove these blank spaces
automatically?

I've tried =TRIM and it doesn't work. Is there something
I can put in Replace to make it remove all blank
characters after the cell value?

 

[Gord Dibben]

In that case, there could be HTML non-breaking space characters(160)in the
data.

You could try the CLEAN Function.

Or to do with a Macro, see David MCritchie's TRIMALL macro for further
cleaning.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Gord Dibben Excel MVP