Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Split Single Column Into Multiple Columns And Rows

1. I agreed to these rules
Join Date
20th August 2008
Posts
2

## Split Single Column Into Multiple Columns And Rows

Hello all, I have a problem to which I am sure there must be a simple excel formula to solve. I have searched and still not found an answer to my problem. Sorry if the answer comes across as being very fundamental but somehow I have missed it. Here is my situation:

I have 300 rows worth of data that looks similar to this, all organized in one column:

John Q. Smith
Programmer
2111 NW 13th St
Anywhereville, USA, 55555
(555) 555-5555
Joe P. Snider
Organizer
5645 NW 45th St
Anywhereville, USA, 55555
(555) 555-5555
Patty Williams
Accountant
6454 NW 34th St
Anywhereville, USA, 55555
(555) 555-5555

As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this.

John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555
Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555
Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555

Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Split Single Column Into Multiple Columns And Rows

Transpose Rows Into Columns

3. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

## Re: Split Single Column Into Multiple Columns And Rows

try

B1:

Copy right + down

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Split Single Column Into Multiple Columns And Rows

k.I.S.S I like it

5. I agreed to these rules
Join Date
20th August 2008
Posts
2

## Re: Split Single Column Into Multiple Columns And Rows

Thank you! -Steve

Excel Video Tutorials / Excel Dashboards Reports

6. I agreed to these rules
Join Date
1st February 2011
Posts
1

## Re: Split Single Column Into Multiple Columns And Rows

hey everyone,

i have the same problem, but very limited knowledge of excel, so i don't understand jindons help tip.

what does this mean? ->

B1:

Copy right + down

Can someone please explain the procedure step by step, for dummies like me?

Thanks a lot!

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno