{"id":145,"date":"2007-12-21T07:13:16","date_gmt":"2007-12-21T11:13:16","guid":{"rendered":"http:\/\/alsplace.aldenbaker.com\/alsplace\/microsoft\/ms-office\/ms-excel\/145\/joining-two-text-columns-changing-case-to-proper\/"},"modified":"2007-12-21T07:17:49","modified_gmt":"2007-12-21T11:17:49","slug":"joining-two-text-columns-changing-case-to-proper","status":"publish","type":"post","link":"https:\/\/alsplace.info\/?p=145","title":{"rendered":"Joining Two Text Columns – Changing case to “PROPER”"},"content":{"rendered":"\n\n\n\n
\n

I teach a class on Power Excel at the University of Akron. Although it is advertised as an advanced class, there are always some basic concepts that the students don't seem to know. I am amazed at how the simplest techniques will cause the most excitement. This is one of those tips.<\/p>\n

Today, Sajjad from Dubai wrote with a question. He has a database with first name in Column A and Last name in column B. How can he merge First Name and Last Name into a single column?<\/p>\n

<\/p>\n

This is one of those questions that you can never find in Excel help, because no one thinks to search for the word "Concatenation". Heck, I don't think any normal person ever uses the word concatenate. If you don't know to search for Concatenate, then you will never learn that the concatenation operator is an ampersand. Start with a basic formula of
  =A2&B2 This will give you the result shown in C2 below:<\/p>\n

<\/p>\n

This is a good start. However, we really should concatenate first name, a space, and last name. Try this formula:<\/p>\n

  =A2&" "&B2<\/p>\n

<\/p>\n

Then, the question is: do you want to scream MICKEY MANTLE, or would you rather say Mickey Mantle? If you want to change the name to proper case, use the =PROPER() function.
  =PROPER(A2&" "&B2) [Note: see my comment to this message.]<\/font><\/p>\n

<\/p>\n

Next, you want to copy the formula down to all of the cells in the column. A shortcut method for doing this is to double-click the fill handle while cell C2 is selected. The fill handle is the darker square dot in the lower right corner of the cell pointer. The dot looks like this:<\/p>\n

When your mouse pointer is near the dot, the mouse pointer changes to a cross like this.<\/p>\n

<\/p>\n

Double click and the formula will be copied down to all of the cells in the range.<\/p>\n

<\/p>\n

Note: Excel uses the column to the left when figuring out how far to copy cells after the double click. If you happened to have a blank cell in B8, this trick would stop at row 7. Leave cialis tadalafil 5mg<\/a> it to Cher to cause a problem.<\/p>\n

<\/p>\n

If this is the case, you might want to grab the fill handle and drag down to all of the rows in order to copy the formula. Note 2: The Proper function is excellent, but it does not properly capitalize last names like McCartney (See cell C7). You will have to manually go through and capitalize the C after the Mc. It would also have a problem with VanHalen. Is this a pain? Yes – but it is easier to fix a few cells than to retype everything in proper case.<\/p>\n

Converting Formulas to Values<\/strong> Now that you have Firstname Lastname in column C, you might be tempted to delete columns A & B. You can't do this yet. If you would delete columns A & B, all of the formulas in column C would change to the #REF! error. This error is saying, "Hey – you told me the value in this column should be from A2 & B2, but you deleted those cells so I don't know what to put here!".<\/p>\n

<\/p>\n

The solution is to change the formulas to values before you delete columns A & B. Follow these steps:<\/p>\n