topic RE: Get Column Number in Excel in Product Forum

Get Column Number in Excel

Devi_PratyushaN — Fri, 17 Apr 2020 12:05:00 GMT

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
------------------------------

RE: Get Column Number in Excel

Jared.Rumball — Wed, 22 Apr 2020 09:56:00 GMT

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
------------------------------

RE: Get Column Number in Excel

RitaReis_Cabrit — Thu, 04 Mar 2021 21:53:00 GMT

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
------------------------------

RE: Get Column Number in Excel

sumire — Fri, 05 Mar 2021 02:58:00 GMT

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
------------------------------

RE: Get Column Number in Excel

Jared.Rumball — Fri, 05 Mar 2021 04:15:00 GMT

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/
------------------------------