Search code examples
excelexcel-formulaseparatorexcel-2019

Adding and trimming spaces before and after separator

If string contain text with one single special character (separator),

How to ad a space after point before separator and trim any space after separator?

Exemple string:

  • .Dolo.rum ipsum primos@ ar.deo
  • J.ust. simple text@ h er.e
  • Another fr.e.e @. exe mpl e

Expect result:

  • . Dolo. rum ipsum primos@ar.deo
  • J. ust. simple text@her.e
  • Another fr. e. e @.exemple
Solution

  • Since an accepted answer can't be deleted hence sharing the solution as mentioned in the first comment,

    =SUBSTITUTE(LEFT(A1,FIND("@",A1)),".",". ")&SUBSTITUTE(MID(A1,FIND("@",A1)+1,LEN(A1))," ","")