Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Split Single Column Into Multiple Columns And Rows

  1. #1
    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.

    Please help! Thanks! -Steve

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

  3. #3
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,574

    Re: Split Single Column Into Multiple Columns And Rows

    try

    B1:

    =Indirect(Address((Row(A1)-1)*5+Column(A1),1))

    Copy right + down

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

  5. #5
    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. #6
    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:

    =Indirect(Address((Row(A1)-1)*5+Column(A1),1))

    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Convert Multiple Columns To Single Rows
    By Ryalut in forum EXCEL HELP
    Replies: 6
    Last Post: June 29th, 2010, 06:46
  2. Replies: 11
    Last Post: May 13th, 2009, 16:12
  3. Split Comma Delimited Single Column To Multiple Columns
    By sunny_90210 in forum EXCEL HELP
    Replies: 1
    Last Post: February 29th, 2008, 08:48
  4. Convert Single Column To Multiple Columns Of X Rows
    By supratwinturbo in forum EXCEL HELP
    Replies: 5
    Last Post: February 24th, 2008, 12:18
  5. Split Single Column Into Multiple Columns
    By dclark0699 in forum EXCEL HELP
    Replies: 2
    Last Post: July 20th, 2007, 00:40

Bookmarks

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