I think the title says it all. I have a column of dollar amounts, complete with dollar signs. I’d like to convert them into numerical values. Is there any way to strip the $s?
Do you still want excel to consider them money? Select the columns, right click on one of them and go to “Format Cells” and then change the symbol drop down box from $ to nothing.
ETA you can change ‘currency’ to ‘number’ while you’re at it if you want to.
No luck. The numbers are from a banking site, and they originally pasted in as protected. I’ve tried copied and pasted as values only and copying to notepad, but the values still aren’t recognized as numbers.
This may be too obvious a solution but did you try doing a Find and Replace and replacing all the $ with nothing?
That’s a good call. Then enter a 1 and Copy/Paste Special->Multiply against the column to get them to be numbers.
I just tried it out, and it looks like the VALUE function works for this.
If you do that, you can then do a Data-Text to Columns and it will reformat them all as number. Or you can use a formula like =int(A2) and it will take the value in cell A2 and format it as a number.
Formula in an open column
=trim(Left(cell reference, 2))
reformat the column as number or currency
Thanks all. I ran out of time yesterday; I’ll continue later.