Need Help Changing Currency Column with Mixed-in Text Numbers to all Currency format

mstuf

Active Member
Joined
Feb 4, 2003
Messages
321
Office Version
  1. 2016
Platform
  1. Windows
I have a worksheet that has a column for Dollar Values. Thru time, rows were added to the worksheet that had that column formatted as text.

I have tried highlighting the column and opening the format cells option box and choosing Currency and confirming but the text cells do not change to currency.
I tried reformatting the column as Number and then reformatting again choosing Currency but again no change to the cells that were formatted as text.
The original currency cells continue appear as they should.

The text cells contain entries in several forms - IE: .39 - 5 - 4.5 - - 8.35 etc.
The text cells are spread throughout the large worksheet.

I have found that if I change .39 to 0.39 manually - with the column formatted as Currency - the format change applies instantly.
Also if I add a decimal point to 5 making it 5. - the format change applies instantly
If I add a 0 to 4.5 making it 4.50 - the change again applies.
but ...
Even if I change 8.35 to Currency format Manually - Nothing happens.
If I change 8.35 to any other format manually and back to Currency - Nothing Happens.
ff I Clear Contents of the Cell and re-enter 8.35 it appears a currency.

I opened a New empty column and formatted it as Currency then copied the other column to it - still a fail.
I tried changing the format to number and to general then pasting it to then new formatted column - still a fail

The Text entries represent about 10000 cells.
I would really appreciate help to figure out how to get this column in its entirety to appear as currency.
Thank you for reading.

Windows 10
Excel Version - Professional Plus 2016
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
As you have discovered you essentially need to change the Column formatting first to something other than text and then effectively re-enter the data. You can get Excel to do the re-enter part for you in a number of ways.
After changing the column formatting to Currency,
(make sure you have something in row 1 of the column, if its blank just put a temporary filler in there)
• highlight the column
• Data > Text to Columns
• Select Delimited but in the next screen UNCHECK any boxes that are checked.
• Hit Finish
You should now be done.

Let me know how you go.
 
Upvote 0
Solution
Works Perfectly

Thank you SO MUCH.

At age 70 , having used so many of my software items from 1999 and recently being forced to move into the 21st century - it can be overwhelming.
So many features, you don't know what they do or if you need them.
 
Upvote 0
Thanks for letting me know. We are here to help if you need it.
At least search engines are much better now. In the old help you needed to know the the right word to search for and that was the bit you didn't know. ;)
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,775
Members
452,353
Latest member
strainu

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
Back
Top