Extract Alphanumeric Value from String

[JonPT]

Hi Power BI Desktop Forum

I'm trying to extract an 8-character alphanumeric value from a column in Power BI desktop.

The pattern to extract is: first 2 characters are lower case a-z, followed by any 6 numbers and can be anywhere in the column.

The extracted 8 character alphanumeric should then be added to a new column

If the input column contains no 8-character (2 a-z and 6 numbers) alphanumeric then output null to the output column.

INPUT_COLUMN

ab123456

zy654321

fg456123

some-text-ab123456

some-zy654321-text

fg456123-some-text

some-text

some-other-text

OUTPUT_COLUMN

ab123456

zy654321

fg456123

ab123456

zy654321

fg456123

null

null

Tried all combinations of Text.Range, Text.PositionOf, Text.Select, Text.Middle, Text.Length, Text.Remove with no success

Really hoping you can help me here.

Thanks in advance

Regards

JonPT

[Kedar_Pande]

In the Power Query Editor, click on "Add Column" and then select "Custom Column."

if Text.ContainsAny([INPUT_COLUMN], {"a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"}) 
then 
try 
Text.Select(Text.Middle([INPUT_COLUMN], Text.PositionOfAny([INPUT_COLUMN], {"a".."z"}, Occurrence.First), 2), {"0".."9", "a".."z"})
otherwise 
null
else 
null

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande

[Kaviraj11 · Accepted solution]

Hi,

Create a custom column  as below

= let
Source = [String],
Pattern = List.First(List.Select(List.Transform({0..Text.Length(Source)-8}, each Text.Middle(Source, _, 8)), each Text.Length(_) = 8 and Text.Middle(_, 0, 2) = Text.Select(Text.Middle(_, 0, 2), {"a".."z"}) and Text.Middle(_, 2, 6) = Text.Select(Text.Middle(_, 2, 6), {"0".."9"})))
in
if Pattern = null then null else Pattern

[JonPT]

Many thanks Kaviraj11 Your solution worked perfectly

[Greg_Deckler]

@JonPT See if this similar solution helps: Solved: Remove first characters that are numbers - Microsoft Fabric Community