Forum Discussion
summing cells with $ sign
Hello,
I am having problems using the normal formula for adding. (ex. =O3+G3 ) if I have $ signs in the mentioned cells. Is there a walk around?
Thanks.
Ideally the cells should contain numbers such as 37 or 123.45, and their number format should be set to a currency format with $. That way, you can simply sum the values.
But if the cells contain text values that start with $, you can use a formula like this:
=SUBSTITUTE(G3,"$","")+SUBSTITUTE(O3,"$","")
Format the cell with the formula as currency. Or if you prefer the result as a text value again:
=TEXT(SUBSTITUTE(G3,"$","")+SUBSTITUTE(O3,"$",""),"$ #,##0.00")
3 Replies
It would be of great advantage if you insert a file (without sensitive data) so that we can see and understand the problem better. thus you can also be helped faster and more precisely.
As described, the problem could be with the cell formatting that cells may have different formatting.How to control and understand settings in the Format Cells dialog box in Excel
https://docs.microsoft.com/en-us/office/troubleshoot/excel/format-cells-settings
I would be happy to know if I could help.
If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.
Nikolino
I know I don't know anything (Socrates)
Ideally the cells should contain numbers such as 37 or 123.45, and their number format should be set to a currency format with $. That way, you can simply sum the values.
But if the cells contain text values that start with $, you can use a formula like this:
=SUBSTITUTE(G3,"$","")+SUBSTITUTE(O3,"$","")
Format the cell with the formula as currency. Or if you prefer the result as a text value again:
=TEXT(SUBSTITUTE(G3,"$","")+SUBSTITUTE(O3,"$",""),"$ #,##0.00")
HansVogelaar Wow what a quick reply.. the substitute formula did work out fine. Thank you very much.
