Forum Discussion

mucrick's avatar
mucrick
Copper Contributor
Feb 15, 2023

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 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

excel
Formulas and Functions
  • mathetes's avatar
    mathetes
    Silver Contributor
    Feb 16, 2023

    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.

Resources