Wednesday, December 21, 2011

Excel Tips: Joining or Concatenating Text Strings

What is Concatenate

Joins several text strings into one text string and up to 30 strings can be joined using syntax below.

CONCATENATE (text1,text2,text3...)
Ampersand & can also be used as calculation operator instead of the CONCATENATE function to join text items.

As shown below, we can get full name by concatenating last name, first name and middle name. I also used IF() and ISBLANK() function so that we can get nice results for blank or empty middle name.

Formula Example 1
=CONCATENATE(B3,", ",A3," ",LEFT(C3,1),IF(ISBLANK(C3),"","."))
Formula Example 2
=B12&", "&A12&" "&IF(ISBLANK(C12),"",LEFT(C12,1)&".")
Double click cells under Full Name(Column D) to see actual formula used.

No comments:

Post a Comment