Custom Column - IF Statement to pull out specific text

[deadshotblue1]

Hi,

I have a column in Excel that includes ID numbers. The format should be 3 letters starting with M, then 2 random letters then 9 numbers. Sometimes, people input some letters, numbers or symbols before which is incorrect so I want to remove these. I want the column to pull out the letter M and 11 characters after it.

In Power Query, under Custom Column, I tried a number of queries including IF statements but they did not help. Would anyone be able to help please?

Thank you in advance

[deadshotblue1]

Hi, thank you for responding. I am using Excel and within it, Power Query. I have posted the example data below. The ID number on the first line is the correct format (MHJ901234573) which is 3 letters (starting with the letter M and two other letters after it) then 9 numbers after. Also, any line that is not in that format should be removed like in line 5. Thank you

First Name	Surname	ID
Phil	Smith	MHJ901234573
Anna	Stevens	K/MDF803926173PX
John	Long	UIMQY736251437
Sarah	Simons	NC731
Peter	Johnson	H/MGS837462510/GH

[ronrsnfld]

You can use a Regular Expression to extract the ID into a separate column (or transform the same column, if you prefer).  Where there is no match, the function will return a null and you can filter that out with Table.SelectRows.

To add the custom function, paste the code below into a Blank Query (Advanced Editor), then rename that query "fnRegex"

//see http://www.thebiccountant.com/2018/04/25/regex-in-power-bi-and-power-query-in-excel-with-java-script/
// and https://gist.github.com/Hugoberry/4948d96b45d6799c47b4b9fa1b08eadf

//RENAME: fnRegex

let   fx=(text,regex)=>
    Web.Page(
        "<script>
            var x='"&text&"';
            var y=new RegExp('"&regex&"','g');
            var b=x.match(y);
            document.write(b);
        </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}

in
fx

You can then Add A Custom column with Regex= M[A-Z]{2}\\d{9}

full M Code without filtering the null

let
    Source = Excel.CurrentWorkbook(){[Name="IDtbl"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name", type text}, {"Surname", type text}, {"ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "extracted ID", each fnRegex([ID], "M[A-Z]{2}\\d{9}"))
in
    #"Added Custom"

[ronrsnfld]

Please share a representative data sample (does not have to be real, but should show similar variations), to better illustrate your problem. Also, what platform are you using? (eg Excel, Power Bi Desktop, something else?)