SOLVED

Convert String to Currency

New Contributor

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!! 

 

 

 

 

4 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@AMcLennan 

Select 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.

@Hans Vogelaar 

As I understood 123 (text or number) shall be converted $1.23

@Sergei Baklan 

Ah - you're correct (of course)!

@Hans Vogelaar Thank you SO much!! This worked. I really appreciate your help!