Format a cell so that the comma is there

Marc

Active Member
Joined
Feb 21, 2002
Messages
388
My boss just gave me a spreadsheet, that was passed on to her. The problem is that we cannot format the cells to use the 1000 separtor (,). Currently the cell looks like 12000.00 we want 12,000.00

I tried using the format cell dialog box, but I just get no change in the displayed value. I'm baffled. She mentioned that this worksheet was produced for her by someone using Impromptu. Might the application stripped the ability to format cells. I can align, bold and underline the cell contents using the toolbar buttons, but when I use the "," button the cell value merely moves to the left slightly. Regards,
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Anne Troy

MrExcel MVP
Joined
Feb 18, 2002
Messages
2,607
Sounds like your values are text and not data.

Force them to become data:

Put a 1 in any blank cell.
Copy that cell.
Select *bad* cells.
Hit Edit-Paste Special-Multiply.
This multiplies your values times 1, which = no change in value.
But, it forces Excel to now see your values as numbers.
Now, format the cells as desired.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
An easier way to convert your values is to choose the Data | Text to Columns... menu command and press [ Finish ].
This message was edited by Mark W. on 2002-11-01 14:08
 

Marc

Active Member
Joined
Feb 21, 2002
Messages
388
Thanks to you both. I tried both methods. Mark, When I tried your approach Excel97 balked and stated that it could only convert one column at a time. Although it does produce the desired result.

I ended up using Anne's solution as it allowed for multiple column conversion.

Thanks Again....For spreading the knowledge, Marc
 

Forum statistics

Threads
1,143,709
Messages
5,720,390
Members
422,282
Latest member
psunith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top