merging columns in Excel?

Discussion in 'Software' started by 1DietPepsiMax, Jul 3, 2010.

  1. 1DietPepsiMax

    1DietPepsiMax Private E-2

    Does anyone know a trick to merging columns in Excel?

    Someone manually typed an Excel doc for me with about 2,000 names & addresses. However, they typed the first name in Column A, then typed the last name of each person in Column B.

    Is there a way to merge the content of Column B into the content of Column A so that you end up with one column containing the content of both?

    That way each first and last name would be together in one cell, instead of in side-by-side cells.

    That is because I want to merge this new Excel file with an older, larger one in which the first and last names are already together in one column.
     
  2. usafveteran

    usafveteran MajorGeek

    Yes, use the CONCATENATE function to join columns A and B. Let's assume you'll put the formula in column C. For row 1, the formula would be =CONCATENATE(A1," ",B1). Now, if you want to convert that to text instead of a formula, you can Copy and then use Paste Special | Values to put the name as text (not formula) in another column, perhaps column D. Now, you can delete columns A, B, and C if they are no longer needed, or perhaps just hide them.

    Alternatively, when you Copy and Paste, select column A as the target, thereby overwriting the contents of column A. However, I think you'll find that deleting or hiding columns A, B, and C is the better technique.
     
  3. baklogic

    baklogic The Tinkerer

  4. usafveteran

    usafveteran MajorGeek

    Yep, that's another way to concatenate cells. That reference does not explain how to convert the formula displaying concatenated cells to text.
     
  5. 1DietPepsiMax

    1DietPepsiMax Private E-2

    Thanks, that works great.

    But now how do I make it do it automatically for the whole list? All ~2,000 rows.
     
  6. usafveteran

    usafveteran MajorGeek

    You can copy the formula in the first row and then paste it to all rows below in one step by selecting all rows under the first row before executing Paste, or by dragging the Fill Handle. A reference: How to Use the Fill Handle in Microsoft Excel
     
  7. 1DietPepsiMax

    1DietPepsiMax Private E-2

    Perfectamundo!

    Thanks man!

    :-D
     

MajorGeeks.Com Menu

Downloads All In One Tweaks \ Android \ Anti-Malware \ Anti-Virus \ Appearance \ Backup \ Browsers \ CD\DVD\Blu-Ray \ Covert Ops \ Drive Utilities \ Drivers \ Graphics \ Internet Tools \ Multimedia \ Networking \ Office Tools \ PC Games \ System Tools \ Mac/Apple/Ipad Downloads

Other News: Top Downloads \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

Social: Facebook \ YouTube \ Twitter \ Tumblr \ Pintrest \ RSS Feeds