Remove spaces and middle name in email address

[junada0]

Hi,

I have a list of email address that has been provided incorrectly with a middle name and space included.

Is there a formula that could remove the space and middle name but leave forename and Surname note the all names start with capital letters which may help to identify and arrange data, example.

Fred PaulSmith@xyz.com should read FredSmith@xyz.com ( removal of middle name the space and 'Paul')
Kim AngelaBrown@xyz.com should read KimBrown@xyz.com ( removal of middle name the space and 'Angela')

Many thanks
G

[XOR LX]

Hi.

Do all addresses have a middle name?

Regards

[junada0]

Hi,

Good point, not all them, my list of emails has a mix of good emails that do not need change and approx 30% have the unwanted space and middlename.

Hope this helps
G

[XOR LX]

Try this:

=IF(SUMPRODUCT(0+(ABS(77.5-CODE(MID(LEFT(A1,FIND("@",A1)),ROW(INDEX(A:A,1):INDEX(A:A,FIND("@",A1))),1)))<13))=3,REPLACE(A1,FIND(" ",A1),1+MATCH(TRUE,INDEX(ABS(77.5-CODE(MID(A1,FIND(" ",A1)+ROW(INDEX(A:A,2):INDEX(A:A,FIND("@",A1))),1)))<13,,),0),""),SUBSTITUTE(A1," ",""))

Copy down as required.

Regards

[junada0]

Hi,

Many thanks for your speedy response and just what I was looking for.

Thanks again.

G

[XOR LX]

You're welcome!

Regards