Krdytkyu

I have a spreadsheet of usernames.

The first and last names are in the same cell of column A.

Is there a formula that will concatenate the first two letters of the first name (first word) and the first two letters of second name (second word)?

For example John Doe, should become JoDo.

I tried

=LEFT(A1)&MID(A1,IFERROR(FIND(" ",A1),LEN(A1))+1,IFERROR(FIND(" ",SUBSTITUTE(A1," ","",1)),LEN(A1))-IFERROR(FIND(" ",A1),LEN(A1)))

but this gives me JoDoe as the result.

Yes; assuming each person only has a First and Last name, and this is always separated by a space you can use the below:

=LEFT(A1,2)&MID(A1,SEARCH(" ",A1)+1,2)

I could only base this answer on those assumptions as it is all you provided.

Or if you want a space to still be included:

=LEFT(A1,2)&" "&MID(A1,SEARCH(" ",A1)+1,2)

This is another way...

• A - Name


• B - =CONCATENATE(LEFT(A1,2),LEFT(RIGHT(A1,(LEN(A1)-FIND(" ",A1))),2))
  

And to round things out, here's a solution that will return the first two characters of the first name, and the first two characters of the last name, but also accounts for middle names.

=LEFT(A1,2)&LEFT(MID(A1,FIND("~~~~~",SUBSTITUTE(A1," ","~~~~~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,LEN(A1)),2)

First off, I'd like to say that PeterH's answer is the simplest and easiest to understand. (Although my preference is to use FIND() instead of SEARCH() - typing two less characters helps in avoiding RSI ;-) )

An alternative answer that neither uses MID(), LEFT() nor RIGHT(), but instead uses REPLACE() to remove the unwanted parts of the name is as follows:

=REPLACE(REPLACE(A1,FIND(" ",A1)+3,LEN(A1),""),3,FIND(" ",A1)-2,"")

Explanation:

The inner REPLACE() removes the characters from the third character of the last name onward, whilst the outer REPLACE() removes the characters from the third character of the first name up until the space.

Addendum 1:

The above formula can also be adapted to allow for a single middle name:

=REPLACE(REPLACE(A1,IFERROR(FIND(" ",A1,FIND(" ",A1)+1),FIND(" ",A1))+3,LEN(A1),""),3,IFERROR(FIND(" ",A1,FIND(" ",A1)+1),FIND(" ",A1))-2,"")

This (long-winded) version allows for any number of middle names:

=REPLACE(REPLACE(A1,FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+3,LEN(A1),""),3,FIND("§",SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-2,"")

Note that, of course, BruceWayne's answer is the simplest and easiest to understand solution that allows for any number of middle names.

Addendum 2:

All of the solutions can be adapted to cater for the case of a single name only by wrapping them within an IFERROR() function like so:

=IFERROR(solution, alternate_formula)

Note that the above is a general case formula, and it might be possible to tailor a more efficient modification to a specific solution. For example, if the requirement in the case of a single name is to join the first two letters with the last two letters, PeterH's answer can be more efficiently adapted like this:

=LEFT(A1,2)&MID(A1,IFERROR(SEARCH(" ",A1)+1,LEN(A1)-1),2)

To allow for the case of an initial instead of the first name (assuming a space or dot is not acceptable as the second character) the following can be used with any solution:

=SUBSTITUTE(SUBSTITUTE(solution, " ", single_char), ".", single_char))

Note that the single character can be either hard-coded or calculated from the name.

Finally, if you really need to cater for a single character only for the full name (!), just wrap the single-name-only formula with another IFERROR(). (Assuming, of course, that the alternate formula doesn't take care of that special case.)

Addendum 3:

Finally, finally (no, really ;-) ) to cater for multiple consecutive spaces, use TRIM(A1) instead of A1.