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:
Using the shortened function:
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.
That’s pretty much what I ended up doing at work, only with 728 rows turning into around 30,000…..