Q&A

# Combine the first character of a cell with another cell

+2
−1

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'):

``````=CONCATENATE(LOWER(MID(A1,1,1)),LOWER( B1))
``````

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?

+2
−0

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

`````` =CONCATENATE(LOWER(MID(A1,1,1)),LOWER(B1))
``````

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

``````=LOWER(MID(A1,1,1))&LOWER(B1)
``````

Then applying the LOWER function twice is inefficient:

``````=LOWER(MID(A1,1,1)&B1)
``````

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

``````=LOWER(LEFT(A1)&B1)
``````

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

Applies equally to Sheets. 