Solved Get Excel Column Representation from Number

Hi, I decided to share object I made, where I solved this problem.

Problem: Getting an excel column value (like AL) from number representation (column 56) up to ZZ column
Input: Number (number)
Output: Column (text)
Code:
Code:
'ABCDEFGHIJKLMNOPQRSTUWVXYZ (26)
dim ab = "ABCDEFGHIJKLMNOPQRSTUWVXYZ"

if number < 27 then
    'one alphabet set only
    column = Mid(ab, number, 1)
else
    'for each alphabet set
    dim wholes = number/26
    if wholes > 26 then
        'more than ZZ ref
        column = "ERROR Number too big!"
        return
    end if

    wholes = int(wholes)
    dim ten = Mid(ab, wholes, 1)
    dim ones = number - 26 * wholes

    'fix for last Z
    if ones = 0 then
        ones = 26
    end if

    'remaining number
    dim one = Mid(ab, ones, 1)

    'concacinate
    column = ten & one
end if
 

Attachments

  • 1.PNG
    12.9 KB · Views: 70
Last edited:

VJR

Well-Known Member
Hi democko_kamil,

A code stage for this could be a bit cumbersome to do so. Instead you can have a permanent design without any code by maintaining the data in a collection.

Yes you need to spend some time to list down the data in this collection but it is far much better than thinking of what code to write and is easier once done.
View attachment 1541116007839.png


And then read the column name by passing the column number. The column number has to be made -1 in the Row Index below because it starts from 0.
View attachment 1541116047663.png


Below is the Input (10) and the Output (J) after running the process.
View attachment 1541116121572.png

You can add your own validations to the process in case of an incorrect input.
 
Hi, thank you for your answer,
of course, this is also a great way to do it.

But I would consider few things.
- I can use this code within whole BP (as an object) and I don't have to copy whole logic to each process
- I don't have to loop the collection, therefore it's faster
- I don't have to write all 625 rows to collection (even through i would only write it once, but still.. it's 625 rows (2 columns))
 

VJR

Well-Known Member
Hi, thank you for your answer,
of course, this is also a great way to do it.

But I would consider few things.
- I can use this code within whole BP and I don't have to copy whole logic to each process
- I don't have to loop the collection, therefore it's faster
- I don't have to write all 625 rows to collection (even through i would only write it once, but still.. it's 625 rows (2 columns))
Hi democko_kamil,

I am not sure whether you are thinking of using this approach but below are my inline responses to your inputs.

- I can use this code within whole BP and I don't have to copy whole logic to each process
The approach that I have posted is inside an object and can be re-used from anywhere in BP.
The Input data item was just to show you, but can be passed as Input parameter to a re-usable object and the column letter will be an Output parameter.

- I don't have to loop the collection, therefore it's faster
There is no loop involved in the above diagram. 'Read collection field' is a direct action available in the Utility Collection VBO.
- I don't have to write all 625 rows to collection (even through i would only write it once, but still.. it's 625 rows (2 columns))
I used the below formula to generate the column name and dragged it in an Excel before using it. Dragging the sequential numbers in place of 56 gives the column name at 56th column.
=SUBSTITUTE(ADDRESS(1,56,4),1,"")
 
Ok, I got it

I still see it as one of solutions.
As I was working with visual basic a lot, I just find it easier to run it through code.

Thank you for you input though, it made me think :)
 
Last edited:

VJR

Well-Known Member
Hi democko_kamil,

I re-read your responses and I now realize that you were trying to post a solution that you already created rather than asking for it. I misinterpreted the word "problem" in your original post indicating that you have a problem in converting an excel number to a letter and are looking for a solution. Please accept my apologies. Of course that's the code based solution to it.
 

Deva393

Member
Hi, I decided to share object I made, where I solved this problem.

Problem: Getting an excel column value (like AL) from number representation (column 56) up to ZZ column
Input: Number (number)
Output: Column (text)
Code:
Code:
'ABCDEFGHIJKLMNOPQRSTUVXYZ (25)
dim ab = "ABCDEFGHIJKLMNOPQRSTUVXYZ"

if number < 26 then
    'one alphabet set only
    column = Mid(ab, number, 1)
else
    'for each alphabet set
    dim wholes = number/25
    if wholes > 25 then
        'more than ZZ ref
        column = "ERROR Number too big!"
        return
    end if

    wholes = int(wholes)
    dim ten = Mid(ab, wholes, 1)
    dim ones = number - 25 * wholes

    'fix for last Z
    if ones = 0 then
        ones = 25
    end if

    'remaining number
    dim one = Mid(ab, ones, 1)

    'concacinate
    column = ten & one
end if



what are the dlls and name spaces you used for this code stage? otherwise are you edited some pre existing vbo copy?
 

timelock

New Member
Thank you very much for this, I encountered an error when using this since the mid and int values needs referencing.
To solve this, just go to your Initialise page, on your object then click the Business Object Properties ( it is the box in the page where you could see your object's name) then in the 'Code Options' Tab input below

External References:
Microsoft.VisualBasic.dll

Namespace Imports
Microsoft.VisualBasic
 
Top