Tuesday, March 15, 2011

Excel: How do I take the first character of 1 cell and prepend it to another cell and place it in a 3rd cell?

Hi

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.

John & Smith = jsmith

How can I do this using Excel?

Thanks all

From stackoverflow
  • =CONCATENATE(LEFT(A1,1), B1)

    Assuming A1 holds 1st names; B1 Last names

  • Personally I like the & function for this

    Assuming that you are using cells A1 and A2 for John Smith

    =left(a1,1) & b1

    If you want to add text between, for example a period

    =left(a1,1) & "." & b1

    Jon Fournier : I agree...I hate having to type "CONCATENATE(..." rather than just the & symbol
    Hobbo : Indeed. I could see the point of a concatenate function if you could pass a range in, but... you can't.
  • Use following formula
    =CONCATENATE(LOWER(MID(A1,1,1)),LOWER( B1))
    for
    Josh Smith = jsmith
    note that A1 contains name and B1 contains surname

    Chris
    ------
    Convert your Excel spreadsheet into an online calculator.
    http://www.spreadsheetconverter.com

0 comments:

Post a Comment