Parse alphabet from number

[ArnabSengupta]

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!

[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

[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

[DavidP · Accepted solution]

Hi @ArnabSengupta 

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

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

Updated version attached.