Excel column string to row number and vice versa











up vote
16
down vote

favorite
5













Excel column letters to actual numbers, if you recall, Excel names its columns with letters from A to Z, and then the sequence goes AA, AB, AC... AZ, BA, BB, etc.
You have to write a function that accepts a string as a parameter (like "AABCCE") and returns the actual column number. And then do the exact reverse, given column number return the column name.




Also verify complexity: $O(log n)$, where $n$ is the input number while $log$ is to base the base being considered (hexa, decimal or binary etc.).



public final class Excel {

private Excel() {}

public static int getExcelColumnNumber(String column) {
int result = 0;
for (int i = 0; i < column.length(); i++) {
result *= 26;
result += column.charAt(i) - 'A' + 1;
}
return result;
}

public static String getExcelColumnName(int number) {
final StringBuilder sb = new StringBuilder();

int num = number - 1;
while (num >= 0) {
int numChar = (num % 26) + 65;
sb.append((char)numChar);
num = (num / 26) - 1;
}
return sb.reverse().toString();
}


public static void main(String args) {
Assert.assertEquals(53, getExcelColumnNumber("BA"));
Assert.assertEquals("BA", getExcelColumnName(53));

Assert.assertEquals(703, getExcelColumnNumber("AAA"));
Assert.assertEquals("AAA", getExcelColumnName(703));

Assert.assertEquals(26, getExcelColumnNumber("Z"));
Assert.assertEquals("Z", getExcelColumnName(26));

Assert.assertEquals(702, getExcelColumnNumber("ZZ"));
Assert.assertEquals("ZZ", getExcelColumnName(702));

}
}









share|improve this question




























    up vote
    16
    down vote

    favorite
    5













    Excel column letters to actual numbers, if you recall, Excel names its columns with letters from A to Z, and then the sequence goes AA, AB, AC... AZ, BA, BB, etc.
    You have to write a function that accepts a string as a parameter (like "AABCCE") and returns the actual column number. And then do the exact reverse, given column number return the column name.




    Also verify complexity: $O(log n)$, where $n$ is the input number while $log$ is to base the base being considered (hexa, decimal or binary etc.).



    public final class Excel {

    private Excel() {}

    public static int getExcelColumnNumber(String column) {
    int result = 0;
    for (int i = 0; i < column.length(); i++) {
    result *= 26;
    result += column.charAt(i) - 'A' + 1;
    }
    return result;
    }

    public static String getExcelColumnName(int number) {
    final StringBuilder sb = new StringBuilder();

    int num = number - 1;
    while (num >= 0) {
    int numChar = (num % 26) + 65;
    sb.append((char)numChar);
    num = (num / 26) - 1;
    }
    return sb.reverse().toString();
    }


    public static void main(String args) {
    Assert.assertEquals(53, getExcelColumnNumber("BA"));
    Assert.assertEquals("BA", getExcelColumnName(53));

    Assert.assertEquals(703, getExcelColumnNumber("AAA"));
    Assert.assertEquals("AAA", getExcelColumnName(703));

    Assert.assertEquals(26, getExcelColumnNumber("Z"));
    Assert.assertEquals("Z", getExcelColumnName(26));

    Assert.assertEquals(702, getExcelColumnNumber("ZZ"));
    Assert.assertEquals("ZZ", getExcelColumnName(702));

    }
    }









    share|improve this question


























      up vote
      16
      down vote

      favorite
      5









      up vote
      16
      down vote

      favorite
      5






      5






      Excel column letters to actual numbers, if you recall, Excel names its columns with letters from A to Z, and then the sequence goes AA, AB, AC... AZ, BA, BB, etc.
      You have to write a function that accepts a string as a parameter (like "AABCCE") and returns the actual column number. And then do the exact reverse, given column number return the column name.




      Also verify complexity: $O(log n)$, where $n$ is the input number while $log$ is to base the base being considered (hexa, decimal or binary etc.).



      public final class Excel {

      private Excel() {}

      public static int getExcelColumnNumber(String column) {
      int result = 0;
      for (int i = 0; i < column.length(); i++) {
      result *= 26;
      result += column.charAt(i) - 'A' + 1;
      }
      return result;
      }

      public static String getExcelColumnName(int number) {
      final StringBuilder sb = new StringBuilder();

      int num = number - 1;
      while (num >= 0) {
      int numChar = (num % 26) + 65;
      sb.append((char)numChar);
      num = (num / 26) - 1;
      }
      return sb.reverse().toString();
      }


      public static void main(String args) {
      Assert.assertEquals(53, getExcelColumnNumber("BA"));
      Assert.assertEquals("BA", getExcelColumnName(53));

      Assert.assertEquals(703, getExcelColumnNumber("AAA"));
      Assert.assertEquals("AAA", getExcelColumnName(703));

      Assert.assertEquals(26, getExcelColumnNumber("Z"));
      Assert.assertEquals("Z", getExcelColumnName(26));

      Assert.assertEquals(702, getExcelColumnNumber("ZZ"));
      Assert.assertEquals("ZZ", getExcelColumnName(702));

      }
      }









      share|improve this question
















      Excel column letters to actual numbers, if you recall, Excel names its columns with letters from A to Z, and then the sequence goes AA, AB, AC... AZ, BA, BB, etc.
      You have to write a function that accepts a string as a parameter (like "AABCCE") and returns the actual column number. And then do the exact reverse, given column number return the column name.




      Also verify complexity: $O(log n)$, where $n$ is the input number while $log$ is to base the base being considered (hexa, decimal or binary etc.).



      public final class Excel {

      private Excel() {}

      public static int getExcelColumnNumber(String column) {
      int result = 0;
      for (int i = 0; i < column.length(); i++) {
      result *= 26;
      result += column.charAt(i) - 'A' + 1;
      }
      return result;
      }

      public static String getExcelColumnName(int number) {
      final StringBuilder sb = new StringBuilder();

      int num = number - 1;
      while (num >= 0) {
      int numChar = (num % 26) + 65;
      sb.append((char)numChar);
      num = (num / 26) - 1;
      }
      return sb.reverse().toString();
      }


      public static void main(String args) {
      Assert.assertEquals(53, getExcelColumnNumber("BA"));
      Assert.assertEquals("BA", getExcelColumnName(53));

      Assert.assertEquals(703, getExcelColumnNumber("AAA"));
      Assert.assertEquals("AAA", getExcelColumnName(703));

      Assert.assertEquals(26, getExcelColumnNumber("Z"));
      Assert.assertEquals("Z", getExcelColumnName(26));

      Assert.assertEquals(702, getExcelColumnNumber("ZZ"));
      Assert.assertEquals("ZZ", getExcelColumnName(702));

      }
      }






      java algorithm number-systems






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Apr 21 at 6:13









      200_success

      127k15149412




      127k15149412










      asked Mar 17 '14 at 2:54









      JavaDeveloper

      4,2952378148




      4,2952378148






















          1 Answer
          1






          active

          oldest

          votes

















          up vote
          20
          down vote













          Your code is basically fine, and your unit tests are good. All I have is nitpicks.



          "Excel" in the method names are a bit redundant.



          In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1). Then the compiler can generate 64 as a constant.



          In getExcelColumnName(), the similarly named variables number, num, and numChar are confusing.



          The complexity O(log n) is correct. (With Big-O notation, the base of the logarithm is an unimportant detail, since the base just scales the logarithm by a constant factor, and constant factors are conventionally discarded with Big-O. For example, O(log_26 n) = O(ln n / ln 26) = O(ln n).)



          public final class ExcelColumn {

          private ExcelColumn() {}

          public static int toNumber(String name) {
          int number = 0;
          for (int i = 0; i < name.length(); i++) {
          number = number * 26 + (name.charAt(i) - ('A' - 1));
          }
          return number;
          }

          public static String toName(int number) {
          StringBuilder sb = new StringBuilder();
          while (number-- > 0) {
          sb.append((char)('A' + (number % 26)));
          number /= 26;
          }
          return sb.reverse().toString();
          }
          }





          share|improve this answer























          • "In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1)". Good point.
            – h.j.k.
            Mar 18 '14 at 0:36













          Your Answer





          StackExchange.ifUsing("editor", function () {
          return StackExchange.using("mathjaxEditing", function () {
          StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
          StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
          });
          });
          }, "mathjax-editing");

          StackExchange.ifUsing("editor", function () {
          StackExchange.using("externalEditor", function () {
          StackExchange.using("snippets", function () {
          StackExchange.snippets.init();
          });
          });
          }, "code-snippets");

          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "196"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f44545%2fexcel-column-string-to-row-number-and-vice-versa%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          up vote
          20
          down vote













          Your code is basically fine, and your unit tests are good. All I have is nitpicks.



          "Excel" in the method names are a bit redundant.



          In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1). Then the compiler can generate 64 as a constant.



          In getExcelColumnName(), the similarly named variables number, num, and numChar are confusing.



          The complexity O(log n) is correct. (With Big-O notation, the base of the logarithm is an unimportant detail, since the base just scales the logarithm by a constant factor, and constant factors are conventionally discarded with Big-O. For example, O(log_26 n) = O(ln n / ln 26) = O(ln n).)



          public final class ExcelColumn {

          private ExcelColumn() {}

          public static int toNumber(String name) {
          int number = 0;
          for (int i = 0; i < name.length(); i++) {
          number = number * 26 + (name.charAt(i) - ('A' - 1));
          }
          return number;
          }

          public static String toName(int number) {
          StringBuilder sb = new StringBuilder();
          while (number-- > 0) {
          sb.append((char)('A' + (number % 26)));
          number /= 26;
          }
          return sb.reverse().toString();
          }
          }





          share|improve this answer























          • "In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1)". Good point.
            – h.j.k.
            Mar 18 '14 at 0:36

















          up vote
          20
          down vote













          Your code is basically fine, and your unit tests are good. All I have is nitpicks.



          "Excel" in the method names are a bit redundant.



          In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1). Then the compiler can generate 64 as a constant.



          In getExcelColumnName(), the similarly named variables number, num, and numChar are confusing.



          The complexity O(log n) is correct. (With Big-O notation, the base of the logarithm is an unimportant detail, since the base just scales the logarithm by a constant factor, and constant factors are conventionally discarded with Big-O. For example, O(log_26 n) = O(ln n / ln 26) = O(ln n).)



          public final class ExcelColumn {

          private ExcelColumn() {}

          public static int toNumber(String name) {
          int number = 0;
          for (int i = 0; i < name.length(); i++) {
          number = number * 26 + (name.charAt(i) - ('A' - 1));
          }
          return number;
          }

          public static String toName(int number) {
          StringBuilder sb = new StringBuilder();
          while (number-- > 0) {
          sb.append((char)('A' + (number % 26)));
          number /= 26;
          }
          return sb.reverse().toString();
          }
          }





          share|improve this answer























          • "In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1)". Good point.
            – h.j.k.
            Mar 18 '14 at 0:36















          up vote
          20
          down vote










          up vote
          20
          down vote









          Your code is basically fine, and your unit tests are good. All I have is nitpicks.



          "Excel" in the method names are a bit redundant.



          In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1). Then the compiler can generate 64 as a constant.



          In getExcelColumnName(), the similarly named variables number, num, and numChar are confusing.



          The complexity O(log n) is correct. (With Big-O notation, the base of the logarithm is an unimportant detail, since the base just scales the logarithm by a constant factor, and constant factors are conventionally discarded with Big-O. For example, O(log_26 n) = O(ln n / ln 26) = O(ln n).)



          public final class ExcelColumn {

          private ExcelColumn() {}

          public static int toNumber(String name) {
          int number = 0;
          for (int i = 0; i < name.length(); i++) {
          number = number * 26 + (name.charAt(i) - ('A' - 1));
          }
          return number;
          }

          public static String toName(int number) {
          StringBuilder sb = new StringBuilder();
          while (number-- > 0) {
          sb.append((char)('A' + (number % 26)));
          number /= 26;
          }
          return sb.reverse().toString();
          }
          }





          share|improve this answer














          Your code is basically fine, and your unit tests are good. All I have is nitpicks.



          "Excel" in the method names are a bit redundant.



          In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1). Then the compiler can generate 64 as a constant.



          In getExcelColumnName(), the similarly named variables number, num, and numChar are confusing.



          The complexity O(log n) is correct. (With Big-O notation, the base of the logarithm is an unimportant detail, since the base just scales the logarithm by a constant factor, and constant factors are conventionally discarded with Big-O. For example, O(log_26 n) = O(ln n / ln 26) = O(ln n).)



          public final class ExcelColumn {

          private ExcelColumn() {}

          public static int toNumber(String name) {
          int number = 0;
          for (int i = 0; i < name.length(); i++) {
          number = number * 26 + (name.charAt(i) - ('A' - 1));
          }
          return number;
          }

          public static String toName(int number) {
          StringBuilder sb = new StringBuilder();
          while (number-- > 0) {
          sb.append((char)('A' + (number % 26)));
          number /= 26;
          }
          return sb.reverse().toString();
          }
          }






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Mar 18 '14 at 1:04

























          answered Mar 17 '14 at 7:02









          200_success

          127k15149412




          127k15149412












          • "In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1)". Good point.
            – h.j.k.
            Mar 18 '14 at 0:36




















          • "In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1)". Good point.
            – h.j.k.
            Mar 18 '14 at 0:36


















          "In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1)". Good point.
          – h.j.k.
          Mar 18 '14 at 0:36






          "In getExcelColumnNumber(), group - 'A' + 1 with parentheses as - ('A' - 1)". Good point.
          – h.j.k.
          Mar 18 '14 at 0:36




















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Code Review Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          Use MathJax to format equations. MathJax reference.


          To learn more, see our tips on writing great answers.





          Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


          Please pay close attention to the following guidance:


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f44545%2fexcel-column-string-to-row-number-and-vice-versa%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          Сан-Квентин

          Алькесар

          Josef Freinademetz