Parse alphabet from number

Hi All,

I have a string in which is is represented as 1A, 2B, 7D etc. I want to replace the alphabets with their numeric position (A with 1, B with 2, C with 3 etc) and with a dot (.) in between (1A= 1.1, 2B= 2.2, 7D=7.4 etc).

I have attached the sample dataset with sheet 2 as the desired output.

Help is much appreciated.

Thank you!

Dataset.xlsx

Parse

Accepted answers

DavidP

Hi @ArnabSengupta

You can just change the regular expression in the Regex tool to

(\d+)([[:alpha:]])

Updated version attached.

convert letters to numbers.yxmd

All comments

DavidP

Hi @ArnabSengupta

You can do this 2 ways:

1. create a lookup table that has A=1, B=2, etc.

2. or you can use the ASCII values and subtract the ASCII values.

Below is a solution for option 2. You don't have to use regex, you can also split the data using Left() and Right() functions

convert letters to numbers.yxmd

Evaldas1

Very nice solution #2

ArnabSengupta

Hi @DavidP

Solution 2 seems quiet right but when I have 2 digit number followed alphabet, it is parsing the digits too.

eg. 19A

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703