Skip to Content
Technical Articles

How to convert an integer into an Excel column ID and back

I meet this requirement from time to time.

Each time I start from scratch. This time I decided to put a note here.

A pretty simple task is to convert a number, say 3 to a column, like C. Or, not less obvious, 1467 to BDK.

Here’s the code for int to column name conversion (without additional checks) and back:





* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZB74H_NEXUS_GSHEETS_READWRITE=>GET_COLUMN_FROM_INT
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_COLUMN_NUM                  TYPE        INT4
* | [<-()] RV_RESULT                      TYPE        STRING
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD get_column_from_int.
    CHECK iv_column_num > 0.
    DO.
      DATA(lv_modiv_column_num MOD 26.
      DATA(lv_diviv_column_num DIV 26.
      iv_column_num lv_div.
      rv_result sy-abcde+lv_mod(1&& rv_result.
      IF iv_column_num <= 0.
        EXIT.
      ENDIF.
    ENDDO.
  ENDMETHOD.





* <SIGNATURE>---------------------------------------------------------------------------------------+
* | Static Public Method ZB74H_NEXUS_GSHEETS_READWRITE=>GET_INT_FROM_COLUMN
* +-------------------------------------------------------------------------------------------------+
* | [--->] IV_COLUMN                      TYPE        STRING
* | [<-()] RV_RESULT                      TYPE        INT4
* +--------------------------------------------------------------------------------------</SIGNATURE>
  METHOD get_int_from_column.
    DO.
      IF iv_column IS INITIAL.
        RETURN.
      ENDIF.
      FIND iv_column(1IN sy-abcde MATCH OFFSET DATA(lv_offset).
      rv_result rv_result * 26 + lv_offset + 1.
      iv_column iv_column+1.
    ENDDO.
  ENDMETHOD.
11 Comments
You must be Logged on to comment or reply to a post.
  • I was little bit confused by blog title. Is it possible to add “Excel column” to specify what is it about? 🙂

    Also found one old method which I used for same thing. Similar approach to yours:

    METHOD get_excel_col.
    
      "input - return
      "1      - A
      "26     - Z
      "27     - AA
      "...........
      "16384  - XFD (maximum columns for 2007+ EXCEL)
    
      DATA: l_col_temp  TYPE i,
            l_mod       TYPE i.
    
      l_col_temp = i_col.
    
      WHILE l_col_temp > 0.
        l_mod = ( l_col_temp - 1 ) MOD 26.
        r_column = sy-abcde+l_mod(1) && r_column.
        l_col_temp = ( l_col_temp - l_mod ) / 26.
      ENDWHILE.
    
    ENDMETHOD.
    • Yep, I second Tomas. You could use the comments from his method (input – return…) would be better in place of “3 to a column, like C. Or, not less obvious, 1467 to BDK.”

      An Excel screenshot too!

      • Hi Sandra,

         

        Not sure if I get your point. I don’t think I need to add a screenshot of an Excel to illustrate an Excel Sheet. Do you think it worth to add it here like a lovely picture with column letters and with numbers? Actually I was amazed to have here 2 comments for a very ordinary blog post! 🙂

         

        I had no feedback for much more complicated solutions.

         

        Kindest regards,

        Alex

        • I post a comment only when I feel it’s needed. The important word “Excel” is missing in your title, it took me 30 seconds to understand what you were talking about. The only two people who react, tell you that it’s unclear, no comment with “thank you” from other persons as usual… Well, okay, do as you wish anyway 🙂

    • Hi Tomas,

      Updated the title. Not sure if didn’t make it worse 🙂

      Right, there are some other ways to do the same ans frankly speaking today I would change a bit the code of myself. But it’s never ending story and the point was to have this logic somewhere to be able to come back and get easily because 30 secs of googling did not give me a good answer. I found a version with recursion (OMG) and stopped looking other options 🙂

       

      Cheers,

      Alex

        • There’s not only the MS Excel table editor. That one is for some money. You can also use Google Sheets where the logic is the same for columns. And there are some more table editors that have the same table structure but are called differently 🙂

          • It was just that “table column char” could be related to absolutely anything, not even closely related to a “spreadsheet” software. Or an example might help understanding like “(e.g. Z -> 26, AA -> 27)”. Anyway, that’s your post, and I referenced your useful post in my notes, so thank you 🙂

  • A little bug with GET_COLUMN_FROM_INT( 26 ), that gives ‘AZ’ instead of ‘Z’.

    Before correction:

          DATA(lv_div) = iv_column_num DIV 26.

    After correction:

          DATA(lv_div) = ( iv_column_num - 1 ) DIV 26.
  • LOL, count me in as a confused one. I was also like “what the heck does this mean” until I realized you mean Excel columns. 🙂

    Sorry, the title is still confusing. “Table column” on SCN = immediate association with a transparent table field. May I suggest “How to convert an integer into an Excel column ID and back”?

    Even if this code might have broader application than Excel I feel mentioning it explicitly will make the purpose perfectly clear from the start.

    Also I’m curious in what business context such requirements occur and whether ABAP2XLSX could help with this?

    Thank you!