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

[mstuf]

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

 

[Alex Blakenburg]

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.

 

[mstuf]

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.

 

[Alex Blakenburg]

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.