cancel
Showing results for 
Search instead for 
Did you mean: 

Get Column Number in Excel

Devi_PratyushaN
Level 3
​​How to get a column number given a cell reference in Excel?

Ex: Cell Reference - F10
Then Column number should be 6 as F is 6th Column.

------------------------------
Devi Pratyusha Nallagonda
------------------------------
4 REPLIES 4

Jared.Rumball
Level 6
Hi,

There is a pre-built component available on the RPA Marketplace that provides this functionality: 
MS EXCEL - CELL REF UTILITIES FREE FOR BLUE PRISM

Take a look. It should do exactly what you need.

Regards,
Jared

------------------------------
Jared Rumball
Executive Director
Q4 Associates Ltd
Pacific/Auckland
------------------------------

Hi there!! I just saw your response and downloaded your Cell Ref Utilities file. What should I do before download? I got the code, but should I create an Object and code that or introduce that as a code stage in my process?

Thanks a lot, 

Rita



------------------------------
Rita Reis Cabrita
------------------------------

sumire
Level 9

Hi ,

You mean  "convert a base26 number to a decimal number".

paste this VB code to your code stage. Input argument is "ColAlpha"(Text) and output argument is "ColNum"(Number).

Add "Microsoft.VisualBasic" and "System.IO" to "Namespace Imports".

Dim AryColAlpha As Char() = ColAlpha.ToCharArray()
Dim LenColAlpha As Integer = ColAlpha.Length - 1

For Each c As Char In AryColAlpha
	Dim Currentc As Integer = Asc(c) - 64
	If Currentc < 1 Or Currentc > 26 Then ColNum = 0

	ColNum += Currentc * CType(Math.Pow(26, LenColAlpha), Integer)
	LenColAlpha -= 1
Next

For example, input  is "AAA", output  is 703. input is "F", output is 6.
Only uppercase alphabets can be allowed as input argument. Please check input argument in advance.
------------------------------
Mitsuko
Asia/Tokyo
------------------------------

------------------------------
Mitsuko
Asia/Tokyo
------------------------------

Hi Rita,

You can just import the object into your Blue Prism environment and call the action(s) directly as you would for any other object. There are no special prerequisites around this object and the actions it provides.

Hope that helps.

Regards,
Jared

------------------------------
Jared Rumball
Q4 Associates Ltd
https://www.q4associates.biz/
------------------------------