Cannot change the format to $ sign because there is space

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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 $?
 
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.
 
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
 
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
 
Back
Top