Combining first two letters from first name and first two letters from last name

Question

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 the last 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.

Answer 1

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)

Answer 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)

_{Thanks to @Kyle for the main part of the formula}

Answer 3

This is another way...

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

Answer 4

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(A1, FIND(" ",A1)+3, LEN(A1), "") removes the characters from the third character of the last name onward, whilst the outer REPLACE(inner_replace, 3, FIND(" ",A1)-2, "") removes the characters from the third character of the first name up to and including 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,"")

by replacing FIND(" ",A1) with IFERROR(FIND(" ",A1,FIND(" ",A1)+1), FIND(" ",A1)).

FIND(" ", A1, FIND(" ",A1)+1) finds the second space (by starting the search for the space after the first space) or errors otherwise.
IFERROR(find_second_space, FIND(" ",A1)) finds the first space if there is no second space.

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,"")

In this case FIND(" ",A1) is replaced with FIND("§", SUBSTITUTE(A1," ","§",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))).

LEN(A1)-LEN(SUBSTITUTE(A1," ","")) counts the number of spaces.
SUBSTITUTE(A1, " ", "§", count_of_spaces) replaces the last space with §.
FIND("§", last_space_replaced_string) finds the first § which is the same as finding the last space.

(The § could, of course, be replaced with any character guaranteed not to exist in the full name string. A more general, safer alternative would be to use CHAR(1). )

Note that, of course, BruceWayne's answer is the simplest and easiest to understand solution that allows for any number of middle names. Well it was. Until I posted my other answer, that is ;-)

---

Addendum 2:

All of the solutions can be adapted to cater for the case of a single name only (if there's a requirement for a four character result) 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 make 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 in this way:

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

To allow for the case of a single letter first name or an initial (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. (Or use "" to remove the space or dot.)

Finally, if you really need to cater for the case where the full name is a single character only(!), 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 and/or leading/trailing spaces, use TRIM(A1) instead of A1.

^{* I'll leave the case for a single letter last name, like Mr T, as an exercise for the reader.}

Hint: =solution &IF(MID(A1,LEN(A1)-1,1)=" ", single_char, "")

Answer 5

Based on this answer, here's an elegant solution which works with any number of middle names:

=LEFT(A1,2)&LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),2)

Explanation:

SUBSTITUTE(A1, " ", REPT(" ",LEN(A1))) replaces the inter-word space(s) with spaces equal in number to the length of the entire string. Using the string length rather than an arbitrarily large number guarantees the formula works for any length string and means it does so efficiently.

RIGHT(space_expanded_string, LEN(A1)) extracts the rightmost word prepended by a bunch of spaces.^*

TRIM(space_prepended_rightmost_word) extracts the rightmost word.

LEFT(rightmost_word, 2) extracts the first two characters of the rightmost word (last name).

^{*Caveat: If it's possible for a username to contain trailing spaces, you need to replace the first argument of SUBSTITUTE(), i.e. A1, with TRIM(A1). Leading spaces and multiple consecutive spaces between words are handled correctly just with A1.}

---

Fixing Your Attempt

Taking a closer look at your attempted solution, it looks like you were very close to a working formula to concatenate the first two letters of the first word (i.e. the first name) and the first two letters of the second word if it existed.

Note that if a username were to contain middle names, the corrected formula would incorrectly grab the first two letters from the first middle name instead of from the last name (assuming your intent is indeed to extract them from the last name).

Also, if all the usernames consist only of either a first name, or a first name and a last name, then the formula is needlessly complicated and can be simplified.

To see how the formula works and so fix it, it is easier if it is prettified, like so:

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

To understand how it works, first look at what happens when A1 contains no spaces (i.e. it contains a single name only). All the IFERROR() functions evaluate to their second arguments since FIND() returns a #VALUE! error if the search string is not found in the target string:

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

The third argument of MID() evaluates to zero, so the function outputs "" and the formula result is the first two characters of the single name.

Now look at when there are exactly two names (i.e there is exactly one space). The first and third IFERROR() functions evaluate to their first arguments but the second one evaluates to its second argument since FIND(" ", SUBSTITUTE(A1," ","",1)) is attempting to find another space after removing the first, and only, one:

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

Clearly, MID() returns the second word (i.e. the last name) in its entirety, and the formula result is the first two characters of the first name followed by all the characters of the last name.

For completeness sake, we'll also look at the case where there are at least three names, although it should be fairly obvious now how to fix the formula. This time, all the IFERROR() functions evaluate to their first arguments:

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

It is slightly less clear than it was in the previous case, but MID() returns exactly the entire second word (i.e. first middle name). Thus, the formula result is the first two characters of the first name followed by all the characters of the first middle name.

Obviously, the fix is to use LEFT() to get the first two characters of the MID() output:

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

The simplification I mentioned above is to replace LEFT(MID(…,…,…), 2) with MID(…,…,2):

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

or on one line:

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

This is essentially PeterH's solution modified to also work with single names (in which case, the result is just the first two characters of the name).

Note: The prettified formulas actually work if entered.