I need some help with a calculated column. My formula looks like this, and so far it is working.
=IF(Column1="Sweden";[Column2];Column3)
But The data shown if Column1 = Sweden, contains a "-" in the middle, I want to remove this "-" from every entry that has Sweden in Column1.
I can't seem to figure this out, I've tried a lot of variations but it does not work for me.
If there is - in the middle of [Column2], you can use below formula:
=IF([Column1]="Sweden",IF(ISERR(SEARCH("-",[Column2],1)),[Column2],REPLACE([Column2],SEARCH("-",[Column2],1),1,"")),[Column3])
Note:
,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).Same formula with ; separator:
=IF([Column1]="Sweden";IF(ISERR(SEARCH("-";[Column2];1));[Column2];REPLACE([Column2];SEARCH("-";[Column2];1);1;""));[Column3])
Update from comments:
Use below formula to show the empty calculated field if Column1 or Column2 is empty:
=IF(OR(ISBLANK([Column1]);ISBLANK([Column2]));"";IF([Column1]="Sweden";IF(ISERR(SEARCH("-";[Column2];1));[Column2];REPLACE([Column2];SEARCH("-";[Column2];1);1;""));[Column3]))
