Forum Discussion
Solved
How can I add dozens of cells of data when each cell starts with $?
I receive an Excel spreadsheet with dozens of rows of dollar figures, but the data in each cell says $XX.xx. The autosum feature on Excel doesn't work, I think because of the "$" sign in each cell. H...
What happens if you
- Select the cells.
- Press Ctrl+H to activate the Replace dialog.
- Enter $ in the 'Find what' box and leave the 'Replace with' box empty.
- Click 'Replace All'.
If you want a formula that will accept dollar amounts as text (and have excel 365) you could use
= "$" & SUM(
VALUE(
TEXTAFTER(amount, "$")
)
)The formula converts the text to numbers, calculates the total, and uses a "$" to reconvert the total back to text. More precise control over the output format could be achieved using TEXT and a number format "$#,##0.00".