Cannot change the format to $ sign because there is space

C

Cynthia Amrit

I have to chage the format of Price 123.42 to $123.42 but in the beggining of
123.42 there is a space and I tried to go to format and click into the $ but
that wont happen.

I have 1000lines where I have to change to 123.42 to $123.42 but because of
the space I have to delete the space then go to format and $ sign.

I can't copy and do paste special it won't let me.

Column A Column B
123.41 $123.41
 
T

toppers

What happens if you format as Currency with $? Or is that what you mean by
"format and $ sign"? Blanks in front of numbers should be ignored
 
C

Cooliegirl2323

I mean when I right click and format I can't get 123.42 to $123.42. Excel
won't allow me.

How can I do this instead of manually typing $?
 
T

toppers

How is cell currently formatted? And what does "Excel not allow me mean"?

Typing 123.45 into a cell, right click, format as Currency with $ symbol
should work.
 
R

Rick Rothstein \(MVP - VB\)

I mean when I right click and format I can't get 123.42 to $123.42. Excel
won't allow me.

How can I do this instead of manually typing $?
Click to expand...

I presume since the cell has a retained blank space, it is text and not a
number. While this is not an ideal solution by any means (it keeps the
leading blank space and doesn't round any values to 2 decimal places), but
it does display whatever text is in the cell with a leading $ sign... use $@
in a Custom Format.

Rick
 
F

Fred Smith

1. Insert a helper column in your data.
2. Use the formula =--trim(a1) to convert your text (' 123.42') to a number
(123.42)
3. Use copy and Paste Special>Values to store the values rather than the formula
4. Format the column as currency
5. Delete the original column
 
Top