Transpose One Column Into Multiple Columns with Excel Macro

Say you’ve got a file from someone that is an address list. Name, address, city etc. But it’s all in one column. It’s not separated into different columns to easily do any analysis or mail merge. You need to put it into a table with a name field or address field. This also can apply to other types of one column data or databases.

There’s a bunch of ways to transform this type of data, and in this video, I’ll show a quick way to do it by recording a macro. It’s not as scary as it sounds. Heck I’m not VBA expert and I could even do it. And if I can do it, so can you.

0:00 Intro
1:18 Enable Developer Tab
1:40 Start Macro Recorder
2:30 Review & Explain Recorded Macro
3:50 Add VBA to Loop
4:41 Add Another Recorded Macro to Existing

📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!

🎁 If you find these videos useful and want to support my channel go to

#excel
#msexcel
#doughexcel

[QuizzerBox]

You Saved Doug. Struggling from 2 days to get This one Thank You Soo much.
Appreciate your work.

[paulbanville]

Thank you Doug. This was my first macros. Unlike so many others, you took it slowly and explained it very well!

[bernardabraminobibas]

Thanks! thats was so quick, i was writing a super long code for that, i didnt know relative references...

[wayneedmondson]

Hi Doug. I love VBA.. but thank goodness for Power Query and worksheet formulas.. right!! Thanks for the code demo. Thumbs up!!

[tanveerbarmare]

Hi Doug, thank you so much man.
This is what i was looking for a long time.

[nischalsingh]

Thank you so much sir, because of you my work got so easier.

[tristanhejny]

Hi Doug! Super helpful and informative video, and it has helped me a lot in building my first couple of macros. I had a quick question on how you would code the macro to not stop at an empty cell, but rather have it copy and paste a number of times equal to a number in another cell (i.e. you only want to repeat the process for the first X addresses, so you type X into cell B1 of your excel sheet and the code will repeat that many times).
If I’m not very clear I do apologize, just starting with macros and I might be attempting to bite off more than I can chew, but thank you in advance!

[forcabarca]

how to make it if not all data complete, for example sometimes street is missing, or zip code missing etc ... I mean for the first row 4 columns, for the second row 5 columns etc...

[jgenterprisesnew]

Good video. I personally use a tool called ASAP which has this transpose function already built-in but I like the macro to clean up the blank cells. I will test it on my next project.

[SaniGarba]

Thanks Doug. We appreciate your modesty regarding knowing little of Macro operations🙂. Just one question, if you could look into it, what happens when we add more of the same data to the one-column list and want to run the macro? Thanks.

[GoogleisDeceptiveLiedtoCollect]

I didn't do anything like this becasue I don't think the data selections lined up, but I goofed and backed it up wrong and am going to do it again. I'm trying this time. I guess I'll just add lines for things to line up if I need to.

[javaarchitect]

I need to do rows to rows

AA#BB
to
AA1BB
AA2BB
AA3BB

Is it possible for me to record macro or something to do this? I have several such rows in table 😞