Function for finding a letter in a number and then changing it to a certain value

[Simon_29]

Hello,

I have a table like this with the Order Number column.1. This column contains numbers that sometimes contain the letters - a, b, c, d, e, f, g .... I would need to find these letters and exchange them for numbers: a = 1 b = 2 c = 3 ... I can find and change one value by right-clicking on the column header and selecting "Replace Values", but I would need a code to find and change multiple letters at once. For example, in the picture below we see the number: 310638736GAO7, where I need to change the letters G, A, 0 after the numbers - 7, 1, 10.

 

Can anyone advise me? I can't solve it. Thank you very much

[mahoneypat · Accepted solution]

Here is a custom function that does that.  Create a blank query, open Advanced Editor and replace the text there with the code below.  In your original query, you can then go to the Add Column tab, invoke custom function and choose this function and choose your OrderNumber column as the input.

// fnReplaceLetters
(inputtext as text) =>
let 
Result = Text.Combine(List.ReplaceMatchingItems(Text.ToList(inputtext), List.Zip({{"A".."Z"}, List.Transform({1..26}, each Number.ToText(_))})), "")
in 
Result

Pat

[Simon_29]

Thank you very much. It worked! 🙂 

[Gabriel_Walkman]

Before addressing your issue, I'd like to point out that with your system, the converted order numbers can cause issues:

Letter K = 11
Letters AA = 11

You see the problem?

[Simon_29]

right now I realized that I would not have such an example of having two identical letters next to each other, so it would be enough if one letter finds and changes it.

[Gabriel_Walkman]

It was just an example. The problem does not require identical letters. Again, just an example:

L = 12

AB = 12

[Simon_29]

Hi,
yes yes, I understand what would probably happen but then how can it be done? I can't believe that no one has tried such a thing yet 🙂