Formulas With Special Characters

[mucrick]

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 occasionally include a "+" (e.g., 5.00+).  I found a formula ("Substitute") that allows for a calculation that will ignore the "+".  What I'd like, however, is that if the raw data cell has a "+" that the "+" is retained in the Calculated Value tab.  Seeking a solution from this team to retain the "+".

Thank you,

mucrick

[#2]

@mucrick 

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.