1

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.

0

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:

  1. Sometimes comma(,) 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]))
3
  • Hi again @Ganesh This formula is amazing, but it returns a "0" if Column1 or Column2 are empty. Is it possible to remove this "0" and just leave it empty? – Sweden Feb 3 at 9:44
  • Check updated answer for this. – Ganesh Sanap Feb 3 at 10:01
  • 1
    Brilliant, thank you very much! – Sweden Feb 3 at 10:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.