Conversion from integer to Excel Column (Base 26) A-Z/AA/AAA etc

Help
1

Hi, how do I design a workflow to find boundaries of an Excel sheet (top left, top right, bottom left, bottom right)? Here is my current workflow, currently I am using Ascii increment but this only works until column Z. How do I modify this so that it works for column AA, BA, AAA etc? Thanks

FindBoundariesBasic.xaml (25.9 KB)

1 Like
2

@DEATHFISH

Excel contains any data or you want just end cell reference of an empty excel?

If your excel has some data in it then
Read it in datatable (dt)
Total rows = Dt.rows.count
Total column = Dt.column.count

If this is empty excel then just press
Ctrl + down arrow to get last cell refrence
Ctrl + right arrow to get last column refrence

3

@rahatadi

Hi, basically I need a workflow that:

  1. Converts a number to Base 26 notation e.g. converts E to 5, 27 to AA, 52 to AZ, 53 to BA and so on.
  2. Does the inverse i.e. parses BA28 to column number 53, row 28… E30 to column 5, row 30 etc.

It should work with any length of column label e.g. A-Z, AA-ZZ, AAA-ZZZ and so on. So I can just shift right one cell and it switches from Z10 to AA10, or shift left one cell from BA11 to AZ11 etc.

5

Any help?

6

Hi,

You can move forward or improve by exploring this initial workflow I created. Input file is any number and output is a series of letter/s equivalent to the column ID in excel.

Let me know your updates.

_test.xaml (36.4 KB)

7

By the way, I used MODULO and DIVISION looped in a do-while for this. I think we can experiment and improve the logic here so we can do the reverse conversion as well.

8

@jessbrian

Hi, thanks for the workflow, it works in most cases but when I input 26, it returns me “A” instead of “Z”, how do I fix this? Also, please check if similar issues occur for ZZ to AAA, AZ to BA etc.

Edit: tested it out, 52 is supposed to translate to AZ but instead it calculates to B

9

Hi,

Thanks for raising this. Can you test again? Update me if this works as well. Kindly mark this as a solution. Thanks.

Btw, we can store the output on a collection and read it inversely. Let’s improve the initial code.

_test.xaml (39.2 KB)

10

@jessbrian

Hi, I tested it out and now 26 is ZZ instead of Z

11

You’re a great tester alright. Kindly check below

_test.xaml (45.2 KB)

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.