We will use Figure 2 to illustrate the process of converting text to column.įigure 2 – Data to convert text to column.This tutorial will walk all levels of Excel users through the process of Converting Text to Columns and Rows.įigure 1 – How to Convert Excel Text to Columns and Rows How to Convert Text to Column This tool helps us to split our data into different columns of the excel sheet. We can use the Text to Column tool to separate values that are not similar into separate columns and rows. Please refer to this article for more information about the INDEX formula in Excel.How to Convert Excel Text to Columns and Rows – Excelchat Example: In cell C10, the INDEX formula returns the value from the first row and first column of the range A1 to G7. The third column contains the actual values, pulled by the formula =INDEX($A$1:$G$7 A10 B10).Also for the column number you always start with number one. You need one column containing the column number (here in column B).So if you data start in row 3, the first number you write is still 1. You need one column containing the row number (here in column A).

The screenshot on the right-hand side shows the necessary preparations. That way you can easily refer to the original table. Basically you have to create the column and row number in additional helper columns. But on the other hand, it’s one of the faster ways (compared to setting up the more complex OFFSET formula like in method 3 below or the INDIRECT formula). Unfortunately, it requires some preparations. You can convert a two-dimensional table into just one column by using the INDEX formula.

Excel convert column to row