First letter of first word and full last word in a cell

[pedad]

I have a spreadsheet which is a dump of our user's full names from an old email server. The names appear to be in the formats Firstname Surname, Firstname1-Firstname2 Surname, or Firstname Surname1-Surname2 depending on whether they have a hyphenated firstname or two surnames hyphenated.

I'm not an Excel guy in an way, shape or form, but I'd like to know if there is a way to create a formula to output the firstname initial and the full surname (for both single and hyphenated surnames, but without the hyphen) + the @domain.com into the column needed to list their email address for the powershell script I'll use to automate the account creation for the new email server.

eg.

"Full Name" (existing data)	"Old Email Address" (existing data)	"New Email Address" (new data based on calc)
Firstname Surname	firstname@olddomain.com	fsurname@newdomain.com
Firstname1-Firstname2 Surname	firstname1-firstname2@olddomain.com	fsurname@newdomain.com
Firstname Surname1-Surname2	firstname@olddomain.com	fsurname1surname2@newdomain.com

 

[DanteAmor]

Hi and welcome to MrExcel.
Try this

varios 08jul2020.xlsm
	A	B	C
1	"Full Name" (existing data)	"Old Email Address" (existing data)	"New Email Address" (new data based on calc)
2	Firstname Surname	firstname@olddomain.com	fsurname@newdomain.com
3	Firstname1-Firstname2 Surname	firstname1-firstname2@olddomain.com	fsurname@newdomain.com
4	Firstname Surname1-Surname2	firstname@olddomain.com	fsurname1surname2@newdomain.com
Hoja16

Cell Formulas
Range	Formula
C2:C4	C2	=LOWER(LEFT(A2)&SUBSTITUTE(TRIM(MID(A2,FIND(" ",A2)+1,99)),"-","")&"@newdomain.com")

 

[sandy666]

with Power Query

Full Name		new
Firstname Surname		fsurname@newdomain.com
Firstname1-Firstname2 Surname		fsurname@newdomain.com
Firstname Surname1-Surname2		fsurname1surname2@newdomain.com

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    TAD = Table.AddColumn(Table.AddColumn(Source, "First Characters", each Text.Start([Full Name], 1), type text), "Text After Delimiter", each Text.AfterDelimiter([Full Name], " "), type text),
    Merge = Table.CombineColumns(Table.ReplaceValue(TAD,"-","",Replacer.ReplaceText,{"Text After Delimiter"}),{"First Characters", "Text After Delimiter"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"new"),
    Lowercase = Table.TransformColumns(Table.TransformColumns(Merge, {{"new", each _ & "@newdomain.com", type text}}),{{"new", Text.Lower, type text}}),
    TSC = Table.SelectColumns(Lowercase,{"new"})
in
    TSC

 

[pedad]

Thanks @DanteAmor
That's perfect! How do I deal with the Irish? (those with a ' like O'Sullivan). Our email prefix structure can't contain any special characters at all except a period (dot) if necessary.

 

[Peter_SSs]

Try

20 07 09.xlsm
	A	B	C
1	"Full Name" (existing data)		"New Email Address" (new data based on calc)
2	Firstname Surname		fsurname@newdomain.com
3	Firstname1-Firstname2 Surname		fsurname@newdomain.com
4	Firstname Surname1-Surname2		fsurname1surname2@newdomain.com
5	Simon O'Donnell		sodonnell@newdomain.com
Names

Cell Formulas
Range	Formula
C2:C5	C2	=SUBSTITUTE(SUBSTITUTE(LOWER(REPLACE(A2,2,FIND(" ",A2)-1,"")),"-",""),"'","")&"@newdomain.com"

 

[pedad]

Thanks @Peter_SSs

=SUBSTITUTE(SUBSTITUTE(LOWER(REPLACE(A2,2,FIND(" ",A2)-1,"")),"-",""),"'","")&"@newdomain.com" appears to be exactly what I'm looking for.

Thank you all!

 

[DanteAmor]

How do I deal with the Irish? (those with a ' like O'Sullivan).

Here is my adjusted formula

varios 08jul2020.xlsm
	A		C
1	"Full Name" (existing data)		"New Email Address" (new data based on calc)
2	Firstname Surname		fsurname@newdomain.com
3	Firstname1-Firstname2 Surname		fsurname@newdomain.com
4	Firstname Surname1-Surname2		fsurname1surname2@newdomain.com
5	Firstname O'Sullivan		fosullivan@newdomain.com
Hoja16

Cell Formulas
Range	Formula
C2:C5	C2	=LOWER(LEFT(A2)&SUBSTITUTE(TRIM(MID(SUBSTITUTE(A2,"'",""),FIND(" ",A2)+1,99)),"-","")&"@newdomain.com")

 

[sandy666]

How about
(without olddomain.com because it's simple)

Name
Brynne Walls
Nora Santos
Casey Fields
Phillip T. Richard
Jessamine Marks
Hayes F. O’Dwyer
Yetta Blair
Laith Russell Mac an Airchinnigh
Gregory C. Hopper
Chris de Búrca
Aretha Z. Hanson
Richard Ballard
Shelley Levy
Chris de Burgh
Jennifer Hebert
Sylvia V. Graham
Neville D. Marquez
Lucius Z. Todd
Ora Chang Wong
Anthony Dominguez
Rama R. MacKenna
Amethyst Ashley
Linus Langley
Dillon Martin
Amber Griffith
Tiger K. Shields
Melinda H. New York
Addison Bell
Randall O. George
Lynn Leach
Daquan A. Harrison
Elliott Beard
Shannon Dict Weber
Clinton N. Petty
Price Ellis
Wylie J. Baxter
Iona Johns
Sylvia Williamson
Joelle G. Mckee
Carissa W. Brown
Yoshio Stevens
Minerva Rodgers
Anthony Richards
Dorothy T. Conrad
Sacha K. Allison
Colorado Humphrey
Tate Kim Dzong Un
Montana H. Houston
Jennifer Fisher
Acton B. Mullins
Bell Z. Terrell
Winter S. Sullivan
Erin Sims
Kirk Norman
Calista Kirby
Aphrodite Golden
Serina X. Lester
Vaughan N. Craig
Ursa Y. Sherman
Zahir N. Simpson
Lee A. Noel
Sylvia Gibson
Amethyst S. Browning
Garth Valencia
Lavinia MacMahon
Felix J. Wise
Gareth McGrath
Leilani E. Gibson
Quemby Z. Pollard
Leah Duffy
Martina Coffey
Blossom Shaffer
Virginia Chandler
Lynn R. Townsend
Mariam Mcgee
Kermit V. Molina
Adria Campos
Dylan R. Gomez
Guy Wilkins
Noel U. Castro
Jared Landry
Suki N. Maldonado
Daryl O'Wise
Kieran T. Hale
Jessamine T. Castaneda
Aubrey O'Harvey
Brittany Petty
Warren Anthony
Hu X. Hampton
Brian Cash
Macey Navarro
Darryl U. Matthews
Felix O. Clarke
Boris Weiss
Moses Z. Copeland
Hasad M. Rodgers
Jerome Alexander
Haley Madden
Evelyn Chaney
Murphy Kelley

 

[Peter_SSs]

Thanks @Peter_SSs

=SUBSTITUTE(SUBSTITUTE(LOWER(REPLACE(A2,2,FIND(" ",A2)-1,"")),"-",""),"'","")&"@newdomain.com" appears to be exactly what I'm looking for.

Thank you all!

You're welcome.