Notifications
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?

0 comments

1 answer

+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.

0 comments

Sign up to answer this question »