Extract Text String with PQ

[AllisterB]

I have a column that includes text consisting of 7 consecutive non-blank characters in the format AAANNNN (Alpha A-Z and Numeric 0-9).
I want to extract this field the 7 characters and place the extracted text string in a new Column. If the AAANNNN pattern does not exist on a rowthen no output is made.

The current M code does not do this. I have also tried an approach using by "Add Columns By Example".

Does anyone have any ideas?

Thank You

By Example (revised.xlsx
	F	G	H
2	ID
3	(ABC:FGH4567)
4	(ABC:HJK4567
5	ABC:GH3456
6	ABC:A KL5678
7	:YUI4567
8	ABC:HJKO1234
9	GHK1234
10	:GHY5672))
11	:GHY567))
12
13
14
15
16	ID	Custom.1	Comment
17	(ABC:FGH4567)		should be FGH4567
18	(ABC:HJK4567	HJK4567	OK
19	ABC:GH3456		Correct treatment as not AAA1234 format
20	ABC:A KL5678		Correct Output as it contains a blank
21	:YUI4567	YUI4567	OK
22	ABC:HJKO1234		Correct treatment as not AAA1234 format
23	GHK1234	GHK1234	OK
24	:GHY5672))		should be GHY5672
25	:GHY567))		Correct treatment as not AAA1234 format
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom.1", each if Text.Length(List.Last(Text.Split([ID], ":")))=7 then  List.Last(Text.Split([ID], ":")) else null)
in
    #"Added Custom"

 

[AllisterB]

This worked fine - thank you

However in my real data if there is a space following th colon it doesn't work - so I have introduced a step to replace ": " with "".

 

[sandy666]

another approach

ID		Result
(ABC:FGH4567)		FGH4567
(ABC:HJK4567		HJK4567
ABC:GH3456
ABC:A KL5678
:YUI4567		YUI4567
ABC:HJKO1234
GHK1234		GHK1234
:GHY5672))		GHY5672
:GHY567))
ABCD123
ABCHJFK1234)*B		JFK1234

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF1 = Table.AddColumn(Source, "Custom", each if Text.Contains([ID], " ") then null else if not Text.Contains([ID], ":") then ":"&[ID] else if not Text.Contains([ID], ")") then [ID]&")" else [ID]),
    ETBD = Table.TransformColumns(IF1, {{"Custom", each Text.BetweenDelimiters(_, ":", ")"), type text}}),
    IF2 = Table.AddColumn(ETBD, "Result", each if Text.Length(Text.Select([Custom], {"A".."Z"})) = 3 and Text.Length(Text.Select([Custom], {"0".."9"})) = 4 then [Custom] else if not Text.Contains([ID], ":") and Text.Length([Custom]) > 7 then Text.End([Custom], 7) else null),
    TSC = Table.SelectColumns(IF2,{"Result"})
in
    TSC

question is: with this example, the target string ( JKO1234 ) in 3-4 format should be extracted from the yellow cell?

 

[AllisterB]

another approach

ID		Result
(ABC:FGH4567)		FGH4567
(ABC:HJK4567		HJK4567
ABC:GH3456
ABC:A KL5678
:YUI4567		YUI4567
ABC:HJKO1234
GHK1234		GHK1234
:GHY5672))		GHY5672
:GHY567))
ABCD123
ABCHJFK1234)*B		JFK1234

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF1 = Table.AddColumn(Source, "Custom", each if Text.Contains([ID], " ") then null else if not Text.Contains([ID], ":") then ":"&[ID] else if not Text.Contains([ID], ")") then [ID]&")" else [ID]),
    ETBD = Table.TransformColumns(IF1, {{"Custom", each Text.BetweenDelimiters(_, ":", ")"), type text}}),
    IF2 = Table.AddColumn(ETBD, "Result", each if Text.Length(Text.Select([Custom], {"A".."Z"})) = 3 and Text.Length(Text.Select([Custom], {"0".."9"})) = 4 then [Custom] else if not Text.Contains([ID], ":") and Text.Length([Custom]) > 7 then Text.End([Custom], 7) else null),
    TSC = Table.SelectColumns(IF2,{"Result"})
in
    TSC

question is: with this example, the target string ( JKO1234 ) in 3-4 format should be extracted from the yellow cell?

I have just returned from a week out of the office

Thank you for your reply - yes the JKO12354 would be the correct result


Allister

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF1 = Table.AddColumn(Source, "Custom", each if Text.Contains([ID], " ") then null else if not Text.Contains([ID], ":") then ":"&[ID] else if not Text.Contains([ID], ")") then [ID]&")" else [ID]),
    ETBD = Table.TransformColumns(IF1, {{"Custom", each Text.BetweenDelimiters(_, ":", ")"), type text}}),
    IF2 = Table.AddColumn(ETBD, "Result", each if Text.Length(Text.Select([Custom], {"A".."Z"})) = 3 and Text.Length(Text.Select([Custom], {"0".."9"})) = 4 then [Custom] else if not Text.Contains([ID], ":") and Text.Length([Custom]) > 7 then Text.End([Custom], 7) else if Text.Contains([ID], ":") and Text.Length([Custom]) >= 7 then Text.End([Custom], 7) else null ),
    TSC = Table.SelectColumns(IF2,{"Result"})
in
    TSC

ID		Result
(ABC:FGH4567)		FGH4567
(ABC:HJK4567		HJK4567
ABC:GH3456
ABC:A KL5678
:YUI4567		YUI4567
ABC:HJKO1234		JKO1234
GHK1234		GHK1234
:GHY5672))		GHY5672
:GHY567))
ABCD123
ABCHJFK1234)*B		JFK1234