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 [email protected]
• J. ust. simple [email protected]
• Another fr. e. e @.exemple

CodePudding user response：

You may try using this formula as shown in image below,

Formula used in cell B1

=CONCAT(
LEFT(SUBSTITUTE(A1,".",". "),FIND("@",A1) 1),
SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("@",A1) 1)," ",""))