Replace last letter with the corrsponding alphabet number

[Haokun]

Hi, I am trying to replace the letter of the Invoice number to the corresponding alphabet number.

For example,

From INV000980A to INV0009801

From 2020809AC to 2020809A3

Please take note that the invoice number can be any form of combination, but it will end with a letter from A to Z, I wish to change to the corresponding numeric number.

Thank you.

[fmvizcaino · Accepted solution]

Hi @Haokun ,

Here is a solution for you. 

Best,

Fernando Vizcaino

[bhrmitra]

Hi Haokun,

Please refer to the screenshot below.

If this solves your issue please mark the answer as correct and also hit the like button, if not let me know! I've attached my workflow for you to download if needed.

Thanks,

Abhra Mitra

[grazitti_sapna]

Hi @Haokun , here is the solution to your problem.

Thanks.

[paulfound]

Hi Haokun,

You could use a formula like this:

REGEX_Replace([From], "\w$", tostring(CharToInt(right([From],1))-64))

• Use regex to find the last letter of the invoice "\w$"
• CharToInt turns the letter into ascii and the code for A = 65, B=66
• Remove 64 so that you get 1 for A, etc
• if it can be lower case these numbers would change, so you would need to upper case the letter before conversion.

[Haokun]

Thanks so much for the Regex, I will test out later.

[Haokun]

Thank you!! that totally works