Play around with email address - get the name and company

[martel_9]

I am working on contacts sheet which I need to get the name from email.
Multiple cases I have to faced and with the help of below post link, I formed the sheet below.
Extract name and company from email address

Case 1: The mail id may contain separators like dot, dash and under-score
Case 2: The mail id may contain numbers
Case 3: The mail id may contain upper case letters without separators

Does anyone has an idea to shorten the steps and fix the last two rows issue.
I aim to do everything in one formula or using vba

Contact Database.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	First Name	Last Name		No numbers Fname	No numbers Lname		Final F-Name	Final L-Name		Company Name	Email Address
2	Fname	Lname		Fname	Lname		Fname	Lname		Hotmail	Fname_Lname@hotmail.com
3	Fname	Lname		Fname	Lname		Fname	Lname		Yahoo	Fname.Lname@yahoo.com
4	Fname	Lname		Fname	Lname		Fname	Lname		Outlook	Fname-Lname@outlook.com
5	Fname	Lname3		Fname	Lname		Fname	Lname		Hotmail	Fname_Lname3@hotmail.com
6	Fname1	Lname		Fname	Lname		Fname	Lname		Yahoo	Fname1.Lname@yahoo.com
7	FnameLname	FnameLname		FnameLname	FnameLname		Fname	Lname		Outlook	FnameLname@outlook.com
8	Fname2Lname	Fname2Lname		FnameLname	FnameLname		Fname	Lname		Yahoo	Fname2Lname@yahoo.com
9	FnameLname5	FnameLname5		FnameLname	FnameLname		Fname	Lname		Gmail	FnameLname5@gmail.com
10	F	L.name5		F	L.name		F	L.name		Icloud	F.L.name5@icloud.com
11	F	L-name5		F	L-name		F	L-name		Msn	F.L-name5@msn.com
Sheet3

Cell Formulas
Range	Formula
A2:A11	A2	=LET(MailID,(LEFT($K2,LEN($K2)-LEN((RIGHT($K2,LEN($K2)-FIND("@",$K2))))-1)),Case1,LEFT($K2,LEN($K2)-LEN((RIGHT($K2,LEN($K2)-FIND(".",$K2))))-1),Case2,LEFT($K2,LEN($K2)-LEN((RIGHT($K2,LEN($K2)-FIND("_",$K2))))-1),Case3,LEFT($K2,LEN($K2)-LEN((RIGHT($K2,LEN($K2)-FIND("-",$K2))))-1),(IF(IF(ISERROR(FIND(".",(MailID))),TRUE)=FALSE,Case1,IF(IF(IF(ISERROR(FIND(".",(MailID))),TRUE)=TRUE,IF(ISERROR(FIND("_",(MailID))),TRUE))=FALSE,Case2,IF(IF(IF(IF(ISERROR(FIND(".",(MailID))),TRUE)=TRUE,IF(ISERROR(FIND("_",(MailID))),TRUE))=TRUE,IF(ISERROR(FIND("-",(MailID))),TRUE))=FALSE,Case3,MailID)))))
B2:B11	B2	=LET(MailID,(LEFT($K2,LEN($K2)-LEN((RIGHT($K2,LEN($K2)-FIND("@",$K2))))-1)),Case1,RIGHT(LEFT($K2,FIND("@",$K2)-1),LEN(LEFT($K2,FIND("@",$K2)-1))-FIND(".",$K2)),Case2,RIGHT(LEFT($K2,FIND("@",$K2)-1),LEN(LEFT($K2,FIND("@",$K2)-1))-FIND("_",$K2)),Case3,RIGHT(LEFT($K2,FIND("@",$K2)-1),LEN(LEFT($K2,FIND("@",$K2)-1))-FIND("-",$K2)),(IF(IF(ISERROR(FIND(".",(MailID))),TRUE)=FALSE,Case1,IF(IF(IF(ISERROR(FIND(".",(MailID))),TRUE)=TRUE,IF(ISERROR(FIND("_",(MailID))),TRUE))=FALSE,Case2,IF(IF(IF(IF(ISERROR(FIND(".",(MailID))),TRUE)=TRUE,IF(ISERROR(FIND("_",(MailID))),TRUE))=TRUE,IF(ISERROR(FIND("-",(MailID))),TRUE))=FALSE,Case3,MailID)))))
D2:D11	D2	=IFERROR(TEXTJOIN("", TRUE, IF(ISERROR(MID($A2, SEQUENCE(LEN($A2)), 1) *1), MID($A2, SEQUENCE(LEN($A2)), 1), ""))," ")
E2:E11	E2	=IFERROR(TEXTJOIN("", TRUE, IF(ISERROR(MID($B2, SEQUENCE(LEN($B2)), 1) *1), MID($B2, SEQUENCE(LEN($B2)), 1), ""))," ")
G2:G11	G2	=PROPER(LEFT($D2,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),$D2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1))
H2:H11	H2	=IF($D2=$G2,$E2,(PROPER(REPLACE($D2,1,LEN($G2),""))))
J2:J11	J2	=PROPER(LEFT(REPLACE($K2,1,FIND("@",$K2),""),FIND(".",REPLACE($K2,1,FIND("@",$K2),""))-1))

 

[6StringJazzer]

How can you guarantee that your list in column K covers all the possible email formats? What about something like JohnFKennedy@whitehouse.gov or FrankLloydWright@architect.com?

 

[martel_9]

How can you guarantee that your list in column K covers all the possible email formats? What about something like JohnFKennedy@whitehouse.gov or FrankLloydWright@architect.com?

Thanks, @6StringJazzer for your reply, There will be no guarantee to cover every case.
In fact, I am trying to cover all possible cases (famous ones) as the mail id does not have a fixed structure.

However, as the excel template that I am trying to design, only includes First and Last Names, your mentioned case and mine in the mini sheet could be resolved by taking the last part (Kennedy) or (Wright) as the last name, while the rest can be in the first name cell (JohnF) or (FrankLloyd).

If we cover that, the exceptional cases will be minimal and can be fixed at the time of appending the contacts.
This formulas -and VBA if needed- will save a huge time for me and my colleagues writing everything manually.

 

[Kerryx]

you could use power query

Book1
	A	B	C	D
2	Fname_Lname@hotmail.com	Fname	Lname	hotmail.com
3	Fname.Lname@yahoo.com	Fname	Lname	yahoo.com
4	Fname-Lname@outlook.com	Fname	Lname	outlook.com
5	Fname_Lname3@hotmail.com	Fname	Lname	hotmail.com
6	Fname1.Lname@yahoo.com	Fname	Lname	yahoo.com
7	FnameLname@outlook.com	Fname	Lname	outlook.com
8	Fname2Lname@yahoo.com	Fname	Lname	yahoo.com
9	FnameLname5@gmail.com	Fname	Lname	gmail.com
10	F.L.name5@icloud.com	F	Lname	icloud.com
11	F.L-name5@msn.com	F	L-name	msn.com
Table1

example code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name", type text}, {"Last Name", type text}, {"Column1", type any}, {"No numbers Fname", type text}, {"No numbers Lname", type text}, {"Column2", type any}, {"Final F-Name", type text}, {"Final L-Name", type text}, {"Column3", type any}, {"Company Name", type text}, {"Email Address", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"First Name", "Last Name", "Column1", "No numbers Fname", "No numbers Lname", "Column2", "Final F-Name", "Final L-Name", "Column3", "Company Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Nonumbers", each Text.Remove([Email Address],{"0".."9"})),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Nonumbers", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Nonumbers.1", "Nonumbers.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Nonumbers.1", type text}, {"Nonumbers.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Nonumbers.1", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Nonumbers.1.1", "Nonumbers.1.2", "Nonumbers.1.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Nonumbers.1.1", type text}, {"Nonumbers.1.2", type text}, {"Nonumbers.1.3", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Nonumbers.1.1", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Nonumbers.1.1.1", "Nonumbers.1.1.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Nonumbers.1.1.1", type text}, {"Nonumbers.1.1.2", type text}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "Nonumbers.1.1.1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Nonumbers.1.1.1.1", "Nonumbers.1.1.1.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Nonumbers.1.1.1.1", type text}, {"Nonumbers.1.1.1.2", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type4", "Nonumbers.1.1.1.1", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"Nonumbers.1.1.1.1.1", "Nonumbers.1.1.1.1.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"Nonumbers.1.1.1.1.2", "Nonumbers.1.1.1.2", "Nonumbers.1.1.2", "Nonumbers.1.2", "Nonumbers.1.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"LName")
in
    #"Merged Columns"