![kutools excel convert column to row kutools excel convert column to row](https://cdn.extendoffice.com/images/stories/doc-excel/change-row-column/doc-change-row-column-16.png)
This is because the number on the row is hard-coded as “1” inside the ADDRESS function. Here is how it’s done: In all cases, we can confidently locate “1” and then replace it with the null string (“”) Note that the SUBSTITUTE function is case-sensitive.Īs mentioned, we will use the SUBSTITUTE function to remove the number portion of the text cell address reference and retain only the column letter. For example, the formula =SUBSTITUTE(“123-456-789”, “-”, “”) will return “123456789” because we specify in the formula to replace every occurrence of “-” in the main text with the null string (“”), thereby removing the dashes. The SUBSTITUTE function is used to replace specific parts of a text with another by means of matching. Convert column numbers to letters Using the SUBSTITUTE Function We will then use the SUBSTITUTE function to remove the number portion of the text and retain only the column letter.įigure 4. With the information provided, our ADDRESS function will return the text string “ A1 ”. Note that the four possible values for the abs_num argument are: 1 (or omitted) for absolute reference 2 for absolute row and relative column reference 3 for relative row and absolute column reference and 4 for relative reference. We will do this with the ADDRESS function, by providing 1 for row number (we can use any row number and this will be hard-coded, or fixed, in the formula), a column number from cell B5, and we will use 4 for the abs_num argument to get a relative reference to the cell address. The first step is to generate an address that carries the column number. The ADDRESS function can be entered as part of a formula or as a parameter within another function. For example, the formula =ADDRESS(1,1) will return the value $A$1. It is a built-in function in Excel that can be used as a look-up function. The ADDRESS function returns a text representation of a cell address. Convert column numbers to letters Using the ADDRESS Function
![kutools excel convert column to row kutools excel convert column to row](https://i1.wp.com/www.alphr.com/wp-content/uploads/2017/08/tech7.jpg)
In our example, the formula in cell C5 will beįigure 3. To convert a column number to an Excel column letter ( i.e, A, B, C, etc.) we will make use a formula that utilizes the ADDRESS and SUBSTITUTE functions.įigure 2. Letter A corresponds to the first column in an Excel worksheet, B to the second column, C to the third column, and so on.įigure 1. In this example, we want to input the column number in the first column of the table then allow the second column to show the corresponding Excel column letter.
#KUTOOLS EXCEL CONVERT COLUMN TO ROW HOW TO#
How to Convert Column Numbers to Letters in Excel Convert column numbers to letters