Looking for a unspecific series of numbers in strings from different columns for a calculated column

[314mp_M0th4]

Hello. I have a Dataset with two string columns. Only one column has a string for each row. In this string is some text and somewhere in this text is a 7 digit number, either split with a widespace or not split at all. How can I parse the string to find the digits?

[Ahmedx]

pls try this

[DOLEARY85]

Hi,

You could try something like this in a custom column in Power Query, just change Column1 to your Column Name:

List.Select(List.Transform(Text.Split([Column1], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) = 7){0}

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[DOLEARY85 · Accepted solution]

Quick thought - this will leave you with errors if the field doesn't contain a 7 digit number. Use try and otherwise to leave the field blank if not found:

try List.Select(List.Transform(Text.Split([Column1], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) = 7){0}

otherwise ""

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[314mp_M0th4]

Thanks, now there was actually a condition I didn't think of, what if the series of digits can only be 7 digits long and there are bits that are longer than 7 and the code shouldn't catch?

[DOLEARY85]

The current formula will only catch the 7 digits if you wanted to modify to capture all digits on the first occurance of digits you could change the = 7 to >0:

try List.Select(List.Transform(Text.Split([Column1], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) >0){0}

otherwise ""

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍