Forum Discussion
Formulas With Special Characters
Hello! I have a sheet with two tabs: Raw data and Calculated (same as the raw data tab, just each cell in the raw data tab multiplied by a markup). See attached. Cells in the raw data tab occas...
Here's one solution:
=IF(RIGHT('Raw Data'!A2,1)="+",
TEXT(SUBSTITUTE('Raw Data'!A2,"+","")*Calculated!$B$1,"0")&"+",
SUBSTITUTE('Raw Data'!A2,"+","")*Calculated!$B$1)
FWIW, you'd already done the hard work. All I did was recognize that those original values on the Raw Data sheet have to be text values, at the very least the ones with the "+" sign, so they can be manipulated and revised using some of the text functions, such as RIGHT and TEXT.