+ Reply to Thread
Results 1 to 4 of 4

i need to convert multiple rows into single column

  1. #1
    Registered User
    Join Date
    08-07-2015
    Location
    dubai
    MS-Off Ver
    office 2010
    Posts
    1

    Smile i need to convert multiple rows into single column

    for example,

    214 235 456 746 852
    741 963 789 123 753
    951 357 654 258 258

    need to convert,

    214
    741
    951
    235
    963
    357
    456
    789
    654
    746
    123
    258
    852
    753
    258

  2. #2
    Registered User
    Join Date
    08-04-2015
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    82

    Re: i need to convert multiple rows into single column

    Use Text to columns from Data tab and select "space"

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: i need to convert multiple rows into single column

    This should do what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A
    B
    C
    D
    E
    F
    G
    2
    214
    235
    456
    746
    852
    214
    3
    741
    963
    789
    123
    753
    235
    4
    951
    357
    654
    258
    258
    456
    5
    746
    6
    852
    7
    741
    8
    963
    9
    789
    10
    123
    11
    753
    12
    951
    13
    357
    14
    654
    15
    258
    16
    258


    If your data starts in row 1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by newdoverman; 08-08-2015 at 11:13 AM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: i need to convert multiple rows into single column

    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    1
    2
    214
    235
    456
    746
    852
    ----
    214
    3
    741
    963
    789
    123
    753
    741
    4
    951
    357
    654
    258
    258
    951
    5
    235
    6
    963
    7
    357
    8
    456
    9
    789
    10
    654
    11
    746
    12
    123
    13
    258
    14
    852
    15
    753
    16
    258
    17


    This formula entered in G2:

    =IF(ROWS(G$2:G2)>COUNTA(A$2:E$4),"",OFFSET(A$2,MOD(ROWS(G$2:G2)-1,3),INT((ROWS(G$2:G2)-1)/3)))

    Copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Convert multiple rows to single row
    By wlowe97 in forum Excel General
    Replies: 4
    Last Post: 02-05-2014, 11:37 AM
  2. Convert multiple rows for a company to a single row
    By Chloe13 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 11-08-2012, 09:55 AM
  3. Replies: 0
    Last Post: 09-26-2012, 10:33 AM
  4. Convert multiple rows to one single column
    By Rashme in forum Excel General
    Replies: 2
    Last Post: 09-12-2012, 03:20 PM
  5. Replies: 4
    Last Post: 07-03-2012, 08:01 PM
  6. Convert multiple row to single rows
    By rukia in forum Excel General
    Replies: 6
    Last Post: 05-20-2010, 04:46 AM
  7. Convert single colum/multiple rows to multiple colums.
    By merlinxl in forum Excel General
    Replies: 15
    Last Post: 06-12-2008, 07:01 AM
  8. Convert rows to one single long column?
    By Spalding in forum Excel General
    Replies: 2
    Last Post: 11-21-2005, 09:39 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1