Jan 07 2022 10:41 AM - edited Jan 07 2022 10:42 AM
Is there a formula I could use to convert a string of numbers to currency and assign the decimal placement?
For example, in a field formatted at "general" is -1273143 but I would like it to read -$12,731.43 or another example is 1227794 but I would like it to read $12,277.94
I would just manually type the desired currency but I need to format an entire spreadsheet. Hoping someone here can help! Thanks in advance!!
Jan 07 2022 12:44 PM - edited Jan 07 2022 01:45 PM
SolutionSelect the cells you want to format.
Select currency from the Number Format dropdown on the Home tab of the ribbon.
If that does not help:
Select one column with such cells.
On the Data tab of the ribbon, select Text to Columns, then click Finish.
Repeat for other columns as needed.
Select an empty cell.
Enter 100.
Copy this cell.
Select the cells with values you want to convert.
Right-click anywhere in the selection.
Select Paste Special... from the context menu.
Under Operations, select Divide.
Click OK.
Select Currency from the Number Format dropdown on the Home tab of the ribbon.
Jan 07 2022 01:39 PM
As I understood 123 (text or number) shall be converted $1.23
Jan 07 2022 01:41 PM
Ah - you're correct (of course)!
Jan 07 2022 03:18 PM
@Hans Vogelaar Thank you SO much!! This worked. I really appreciate your help!
Jan 07 2022 12:44 PM - edited Jan 07 2022 01:45 PM
SolutionSelect the cells you want to format.
Select currency from the Number Format dropdown on the Home tab of the ribbon.
If that does not help:
Select one column with such cells.
On the Data tab of the ribbon, select Text to Columns, then click Finish.
Repeat for other columns as needed.
Select an empty cell.
Enter 100.
Copy this cell.
Select the cells with values you want to convert.
Right-click anywhere in the selection.
Select Paste Special... from the context menu.
Under Operations, select Divide.
Click OK.
Select Currency from the Number Format dropdown on the Home tab of the ribbon.