LOADING

Follow me

Merge multiple columns into a new column in Excel
September 15, 2010|BlatheringsDevelopment

Merge multiple columns into a new column in Excel

I was asked today to help out a colleague with merging 3 columns in an Excel spreadsheet into a single column for better formatting into a mailing address. Seems simple enough, right? So I thought.

So I headed downstairs engulfed fully in the assumption that this would take just a couple of minutes, and that I would be able to easily accomplish this with a simple function. After about 30 minutes of messing around, thumbing through Google results, and trying like hell to get a VB Macro to work, another colleague schooled in the wizardry of Excel gives me the solution in about 2 minutes flat. F00ker.

I was on the right path using concatenation and a function to combine columns A through C into column D. The function is this: =CONCATENATE(A1,” “,B1,” “,C1). This is easy to do with the built-in function builder, but could also be written like: =(TRIM(A1&” “&B1&” “&C1)). Trim isn’t necessary, but it’s nice to have if you have an empty cell in the column or extra spaces. The part that made it really easy is the expanding box around the selected cell. There’s a little box in the lower right corner of the bounding box. Double clicking it will apply that formula to the entire column. VIOLA! Naturally you could drag the selection too, but since we wanted the entire column it worked out perfect.

Check out the image sequences below for the two ways I outlined above to merge these columns together. The best part? No Macros, no VB script editing required.

Using the function builder:

Excel spreadsheet excel function builder dialogue box excel function builder dialogue box with built concatenate function The columns merged together in a new column in excel Excel columns merged into new column

Using the shortened function:

Excel columns merged into new column

2 comments
Share

2 comments

  • September 17, 2010 at 9:41 pm

    I tried this at work today but unfortunately I couldn’t remember part of the formula. I ended up writing 60 versions anyways.
    Hope all is well in your life. Take good care.

  • September 19, 2010 at 4:38 am

    That’s pretty much what I ended up doing at work, only with 728 rows turning into around 30,000…..

  • Comments are closed.