+ Reply to Thread
Results 1 to 5 of 5

Insert Columns Between Multiple Existing Columns

  1. #1
    Registered User
    Join Date
    12-30-2009
    Location
    Fresno, CA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Insert Columns Between Multiple Existing Columns

    I have a spreadsheet with thousands of columns. Due to poor planning on my part I need to insert a column between each existing column (e.g. I need to insert a column between the existing columns A and B, B and C, etc...). I would rather not have to do this manually thousands of times. Is there an easy way to do this?

    Thanks in advance.
    Last edited by andrew1830; 12-30-2009 at 10:38 PM.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016
    Posts
    6,846

    Re: Insert Columns Between Multiple Existing Columns

    Try this...
    Assuming you have 2000 columns of data, in B2:BXY1000

    If there isn't a blank row above your data, insert one
    Then...
    •Beginning in the blank cell above the first data column (B1),
    enter a series from 1 through however many data columns you have
    (in my example the series is 1 through 2000 in B1:BXY1)
    •Copy that series
    •Paste it to the right of the series, beginning in BXZ1
    •Select all of the data, including all of the numeric series
    •From the ribbon:
    •••Home.Sort_&_Filter,Custom_Sort
    ••••••Click: options.Sort_left_to_Right....Click: OK
    ••••••Sort by: Row_1
    ••••••Sort by: Values
    ••••••Sort by: Smallest to largest
    ••••••Click: OK

    Since the number series has two 1's, two 2's, etc...
    blank columns will be sorted into your data.

    Is that something you can work with?
    Ron
    Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    12-30-2009
    Location
    Fresno, CA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Insert Columns Between Multiple Existing Columns

    Thanks for the help Ron.

    That tip worked great.

    On a side note, my formulas in the columns to the right of column B all referred to the data in column B. When I ran the sort the formulas didn't automatically update to reference column C (where the original column B ended up). So, I did a find and replace to change "B" in the formulas to "$C" before running the sort and everything turned out perfectly.

  4. #4
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016
    Posts
    6,846

    Re: Insert Columns Between Multiple Existing Columns

    I'm glad that worked for you...thanks for letting us know.

  5. #5
    Registered User
    Join Date
    02-09-2017
    Location
    Finland
    MS-Off Ver
    2013
    Posts
    1

    Re: Insert Columns Between Multiple Existing Columns

    Quote Originally Posted by Ron Coderre View Post
    Try this...
    Assuming you have 2000 columns of data, in B2:BXY1000

    If there isn't a blank row above your data, insert one
    Then...
    •Beginning in the blank cell above the first data column (B1),
    enter a series from 1 through however many data columns you have
    (in my example the series is 1 through 2000 in B1:BXY1)
    •Copy that series
    •Paste it to the right of the series, beginning in BXZ1
    •Select all of the data, including all of the numeric series
    •From the ribbon:
    •••Home.Sort_&_Filter,Custom_Sort
    ••••••Click: options.Sort_left_to_Right....Click: OK
    ••••••Sort by: Row_1
    ••••••Sort by: Values
    ••••••Sort by: Smallest to largest
    ••••••Click: OK

    Since the number series has two 1's, two 2's, etc...
    blank columns will be sorted into your data.

    Is that something you can work with?
    Thank you Ron!

    So many years I added manually x-columns, and now 2017 I found your Post from 2009.

    Thank you again.
    Patrick

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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