Combine the first character of a cell with another cell


A Q on Stack Exchange from a very long time ago included:

I have first names in one column and second names in another, I want to create a third column that contains the first character from the first name and add it to the surname creating first initial + surname.

The user asked "How can I do this using Excel?" and gave this example:

John & Smith = jsmith

So far there have been six As posted, of which only one seems fully to respect the given requirements (though it was another A that was 'Accepted'):


This makes standard assumptions about the location of the data (A1 for first name, B1 for surname) and the delimiter (comma).

The formula works but seems uncharacteristically lengthy for such a simple requirement, and is perhaps more opaque than necessary.

Is there a shorter and/or clearer formula to the same effect?

Why should this post be closed?


1 answer


Regarding length, the correct answer used 43 characters, but one was a space serving no purpose, so say 42 for:


Most obviously, the concatenating function is a great deal less compact than an &:


Then applying the LOWER function twice is inefficient:


Finally, since without parameters, LEFT() is easier to read than MID():


This is less than half the length and, at least for me, clearer.

Applies equally to Sheets.


Sign up to answer this question »