Generating username from first name and last name in excel

Question

generate usernames using First Word of first name, Last initial and Last 4 digit of Reference # and with no space or dash.

First Name: John Franklin
 Last Name: Smith Turner    
 Reference: 123456789
     Email: johns6789@example.com <--Generated email all lowercase, no space

=LEFT(A2,FIND(" ",A2)-1)&LEFT(B2,1)&RIGHT(C2,4)&"@example.com"

I tried the following above but works for only the first email but rest shows #VALUE! error

Ty!

Edit: Added a screenshot

Answer 1

How to automatically merge first and last names in Excel
Fill in the first and last names manually in an adjacent column for the first entry.
Start entering in the following row's name, and Excel will propose full names for the entire column.
To accept the suggestions, press Enter. Done!

Formula 1: In Excel, combine the first and last names.
Let's imagine you have one column for given name and another for surname in your worksheet, and you want to combine these two columns into one.

Here are the Excel formulas to combine first and last names in their most basic form:

=first_name_cell&" "&last_name_cell
CONCATENATE(first_name_cell," ",last_name_cell)