CoolData blog

4 January 2010

Complex formula for name concatenation in Excel

Filed under: Excel — Tags: , , , — kevinmacdonell @ 3:42 pm

One common and very time-consuming task I perform in Excel is creating one clean-looking column of names out of half a dozen columns of name data. It’s the nature of the beast: Every time we pull data on individuals, we’re going to have prefixes (Dr., Rev., etc.), nicknames, and first, middle and last names as separate bits of data. Stringing names together using simple concatenation is not difficult, but the absence of data in some of these columns (prefix, middle name, and nickname) makes the process hard to automate. If you just string the whole row together, you’ll end up with extra spaces and other anomalies.

Here’s a formula that uses nested conditional statements to account for empty cells:

=SUBSTITUTE(TRIM(A2 & " "& B2 & " " & C2 & " " & IF(LEN(B2)=1," ", IF(LEN(D2)=0,"",IF(B2=D2," ","("&D2&")"))) & " " & E2), " ", " ")

Yes, it’s scary, but it works.

(For the record, my nickname is NOT Kev.)

Your columns MUST be in this order to work:

A: Prefix
B: First name
C: Middle name
D: Preferred first name or Nickname
E: Last name

Here’s what the formula does:

  • Concatenates the whole name from whatever “parts” are available
  • Automatically removes extra spaces from the concatenation for cells that are empty (for example, if there’s no Prefix)
  • If the first name is a single initial (see note below), then the first part of the name is the initial plus the middle name
  • If the first name is the same as the preferred first name (nickname), then column D is skipped
  • If the first name is different from the preferred first name (if it is present), then the value of column D is expressed in parentheses

NOTE: By data-entry convention, our Banner records express single initials without a period, therefore the test for a single initial is “length = 1”. If your data includes an initial, you’ll have to modify this to LEN(B2) = 2, which might cause a problem with names that are two letters in length.

Your first column might not be Prefix. In fact it probably isn’t: “ID” is probably your first column. In that case, it might be easier to paste the formula into a simple text editor and shift all the letters however many places to the right that you need, before using it in Excel, like so:

=SUBSTITUTE(TRIM(B2 & " "& C2 & " " & D2 & " " & IF(LEN(C2)=1," ", IF(LEN(E2)=0,"",IF(C2=E2," ","("&E2&")"))) & " " & F2), " ", " ")

This formula, and others like it, would be especially useful to save as a sort of template, if you frequently pull data that has the same columns. This idea was suggested to me by Sarah Ellison, Development Research Associate at the Curtis Institute of Music:

As long as you can get your data into an Excel spreadsheet with the same columns in the same order (she uses Raiser’s Edge, with saved queries and exports), you can take some of the hassle out of using Excel for reporting. She recommends that you have a tab/worksheet with all of your raw data (exactly the way it gets exported from the database), and another tab/worksheet with the formulas to make your page look the way you want it. Just ensure that all your cell references refer to cells in the other tab, and don’t overwrite the formulas sheet when you update/save over the page with the raw data.

Advertisements

7 Comments »

  1. Try the concatenate command (=concatenate A2,B2,C2) If you need to put a space or some punctuation in you just do that in quotes as in =concatenate (A2,”,”,B2,” “,C2) Easier than writing your own formuala – been using it for years.

    Comment by janice — 6 January 2010 @ 3:55 pm

    • oops made a mistake s/b -=concatenate(A1,B1,C1) forgot the parens since I was already typing one!

      Comment by janice — 6 January 2010 @ 3:56 pm

      • Hi there – I do like the elegant simplicity of your solution – =CONCATENATE(A2,” “,B2,” “,C2,” “,D2) – it’s a lot easier on the head than what I’ve got. The difference is that my formula takes out extra spaces that result from empty cells. It also composes the full name differently depending on the presence or absence of a nickname (or preferred first name), or if the nickname/preferred is the same as the first name.

        Comment by kevinmacdonell — 6 January 2010 @ 4:25 pm

        • HOWEVER, you can use TRIM to remove the spaces, like so:

          =TRIM(CONCATENATE(A2," ",B2," ",C2," ",D2))

          I didn’t know this before today – but I know I’ll be using it in future! Thanks again.

          Comment by kevinmacdonell — 7 January 2010 @ 9:15 am

          • I will remember that one too..I am all about working smarter not harder.

            Comment by janice — 7 January 2010 @ 9:25 am

  2. how to transpose multiple data to same name and don’t appear repeate data ? for example : – Input –
    T406 100
    T406 962
    T406 901
    T406 055
    T406 109
    T406 118
    Output –
    T406 100,962,901,055,109,118

    Comment by boby — 12 August 2010 @ 8:28 am

    • Aha, yes, a common problem in working with Excel is how to concatenate multiple rows to eliminate duplicates. I’ve found that the most efficient way to do this involves customizing a VBA script — that’s “Visual Basic”. It’s a lot easier to understand if you do a little programming, which I don’t, but I have had luck in copying bits of code that I find on the net and customizing it into my own script. I don’t have a script handy right now, and anyway many variations are possible, but try searching the internet for something written for Visual Basic and Excel.

      Comment by kevinmacdonell — 12 August 2010 @ 8:53 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: