How can I pass a fuction which correct all domains to @mail.com. I know I have to use =RIGHT(,9) but when you reach the last error it does not apply

CodePudding user response：

Try below formula-

=ArrayFormula(IF(A2:A="",,QUERY(SPLIT(SUBSTITUTE(SUBSTITUTE(A2:A,"mail","|"),"@",""),"|"),"select Col1",0)&"@mail.com"))

This should also work.

=INDEX(IF(LEN(A2:A),QUERY(SPLIT(SUBSTITUTE(SUBSTITUTE(A2:A,"mail","|"),"@",""),"|"),"select Col1")&"@mail.com",""))

CodePudding user response：

Answer

The following formula should produce the results you desire. It assumes that the data you provide is in cells A2:A5 of your spreadsheet. If this is not the case, adjust the A2:A5 portion of the formula appropriately.

=ARRAYFORMULA(REGEXREPLACE(A2:A5,"[@|2].*","@mail.com"))

Explanation

This formula uses REGEXREPLACE to get rid of all rogue characters and replace them with @mail.com. The first argument of REGEXREPLACE is the string to be evaluated. In this case, that is the range from A2 through A5. The second argument is which characters to look for. In this case that is all characters (done using .*) that follow either an at-sign or a numeral two (done using [@|2]). The third argument is which new string to replace the found characters with. In this case that is @mail.com, the correct domain without typos.

The REGEXREPLACE is wrapped in =ARRAYFORMULA because normally REGEXREPLACE can only be used with a single cell rather than a range of cells.

Please note that this solution relies on the assumption you stated that "Everything before @ or 2 is correct."