Aug 15 2020 07:52 AM
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.
Aug 15 2020 08:03 AM
SolutionIdeally 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")
Aug 15 2020 08:05 AM
@Hans Vogelaar Wow what a quick reply.. the substitute formula did work out fine. Thank you very much.
Aug 15 2020 08:10 AM
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.
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)
Aug 15 2020 08:03 AM
SolutionIdeally 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")