How to Split 1 Row into Multiple Columns in Microsoft Excel 2010

In this article, you will learn how to split 1 row into multiple columns.

If we have a row that contains values & we require copy them into multiple columns meeting the criteria.

We will use a combination of 4 functions i.e. INDEX, MOD, ROWS, COLUMNS

 

INDEX: Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Syntax: =INDEX(array,row_num,column_num)

array: Array is a range of cells or table.

row_num: It is the row number in the array from which to return the value.

column_num: it is optional. It is the column number in the array which is used to return the value.

 

MOD: Returns the remainder after a number is divided by a divisor.

Syntax =MOD(number,divisor)

number: It is a numeric  value whose remainder you want to find.

divisor: It is the number which is used to divide the number argument.

 

Rows: It returns the number of rows in a reference or array

Syntax: =(array)

array: Array is a reference to a range of cells or an array.

 

Columns: Returns the number of columns in a reference or array

Syntax: =(array)

array: Array is a reference to a range of cells or an array.

 

Let us understand with an example:

Row# 1 contains numbers such as 1,2,3 & so on till 20

Following is a snapshot of the required output
 
img1
 
The criterion is range A3:A7 should contain value stored in cell A1:E1.

After every 5 cells in the first row; the next 5 values (F1:J1) need to be stored in the range B3:B7 & so on.

The formula in cell A3 would be =INDEX($1:$1,,1+MOD(ROWS($F$3:F3)-1,5)+5*(COLUMNS($E$3:E$3)-1))

Copy down & across the formula, we will get the desired result.

 

In this way, you can get the values in multiple columns from one row meeting criteria.

 

 

 

Users are saying about us...

  1. I’m trying to use this formula/logic to do almost the same thing, but with a long column instead of a long row. In my case I want to make every 15th line a new column where the (numbers, in this case) will continue at the top of each column.

    What I cannot for the life of me figure out is what, in your example, does “ROWS($F$3:F3)” refer to? Why would you need those values? Do you just need some value in order to do the math, and it could be any cell as a starting point? (i.e. “ROWS($D$27:D27)”)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube