Forum Discussion
Simon_29
Helper II
Helper IIFunction for finding a letter in a number and then changing it to a certain value
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 ex...
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 ResultPat
mahoneypat
Microsoft Employee
Microsoft EmployeeHere 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_29Helper II
Helper IIThank you very much. It worked! 🙂