1

enter image description hereHow can I 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

MaSx
  • 11
  • 1
  • 3
  • 3
    change `LEFT(A2,FIND(" ",A2)-1)` to `LEFT(A2,FIND(" ",A2 & " ")-1)` – Scott Craner Jan 25 '18 at 20:18
  • How is your data structured? Are **First Name:, Last Name:, Reference:**, and **Email:** in Column A with data being filled across the sheet horizontally (e.g. into Columns B, C, D, etc.)? If you could add a screenshot, that would be incredibly helpful. – TotsieMae Jan 25 '18 at 20:34
  • I gave you the answer, did you try it? – Scott Craner Jan 25 '18 at 20:57

4 Answers4

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

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

This does the trick for me:

=LOWER(IF(ISERROR(FIND(" ",A2,1)),A2,LEFT(A2,FIND(" ",A2,1)-1)))&LEFT(B2,1)&RIGHT(C2,4)&"@example.com"
infinitezero
  • 1,610
  • 3
  • 14
  • 29
  • 1
    Your first `LEFT` function only accounts for a first name that has four letters, but the OP's question asks for the full first name. Assuming there is more data to this set and that people have more than 4 letters in their first name, this won't work. – TotsieMae Jan 25 '18 at 20:31
  • To further explain and increase understanding...: The OP and @ScottCraner both used the `FIND` function to look for the first space in the name. `FIND` returns a number value as to where that character (" ") was found (in the example, it would return 5). By subtracting 1 from that, we end up with 4, which then tells the `LEFT` function how many characters to return. `SEARCH` can be used instead of `FIND` as they are identical with the exception that `SEARCH` isn't case-sensitive while `FIND` is. – TotsieMae Jan 25 '18 at 20:40
0

Your formula should be:

=LOWER(LEFT($A2,IFERROR(SEARCH(" ",$A2)-1,LEN($A2)))&LEFT($B2,1)&RIGHT($C2,4)&"@example.com")

You can then drag that down your Column D to get your desired result.

Here I used it with test data I made up:

Result

TotsieMae
  • 835
  • 6
  • 17
0

To avoid unusable spaces between "words" , at the beginning or of the end, include in formula an TRIM function

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

Community
  • 1
  • 1
vigilent
  • 1
  • 1