Power Query Extract

[gheyman]

I need to exact any words that start with Q

If I use;
= Table.AddColumn(#"Renamed Columns", "Text Between Delimiters", each Text.BetweenDelimiters([Extract Q_Number], "Q", " "), type text)
it removes the "Q" because this say to extract between.

Is there a way to do this and keep the "Q"?

Thanks

 

[gheyman]

if Text.BetweenDelimiters([InternalRefNum], "Q", " ") = "" then "" else "Q" &Text.BetweenDelimiters([InternalRefNum], "Q", " ")

This works but if you have a different way, I would like to learn. Thanks

 

[GraH]

Text.StartsWith ( string, "Q")?

 

[JGordon11]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclQoLM1MzlYIyC9PLVIILE0tqlSK1YlWCizNTC1RKMhJTSxOBQu4JOYBpRMzc4AaUotLMvPzipViYwE=", BinaryEncoding.Base64), Compression.Deflate))),
    Result = Table.AddColumn(Source, "Q words", each Text.Combine(List.Select(Text.Split([Column1], " "), (x)=> Text.Upper(Text.Start(x,1)) = "Q"), ", "))
in
    Result

 

[GraH]

Was without Excel before

Assuming data may look like below

Book1
	C
2	InternalRefNum
3	n59545J-26940
4	[67615j-31431
5	i18817Q-73719
6	H43547u-19019
7	O32633j-21372
8	w79989O-88946
9	B73014X-91704
10	q88163L-24254
11	j87661g-45742
12	C29617E-17256
13	a70784k-15288
14	Q92116e-88077
15	R92323h-44607
16	J20434a-77659
17	b20201A-90581
18	f94345K-68380
19	s10736_-83270
20	W92586d-99789
21	\73345^-89845
22	P29691F-97103
23	s44986G-23326
24	A74546v-44310
25	Q74182j-13054
Sheet1

using this query

let
    Source = Excel.CurrentWorkbook(){[Name="RefNrs"]}[Content],
    Type_txt = Table.TransformColumnTypes(Source,{{"InternalRefNum", type text}}),
    Select_q_nrs = Table.SelectRows(Type_txt, each Text.StartsWith([InternalRefNum], "q", Comparer.OrdinalIgnoreCase))
in
    Select_q_nrs

returns

Book1
	C
28	InternalRefNum
29	q88163L-24254
30	Q92116e-88077
31	Q74182j-13054
Sheet1

Following the idea by @JGordon11, there can more items in the field.

Book1
	E
2	InternalRefNum
3	A45076v-30743 t77379l-11233 o42191L-80002 ]51075W-72440
4	q88163L-24254 m72023a-66105 g63783g-68472 v58874D-49286
5	a44397R-82559 \97110r-44940 [19987J-66245 B15419p-12519
6	g42997\-10003 Z74920r-94253 w99519l-69687 k32702t-15909
7	H58315O-93218 m36787c-99256 _31963s-25208 y92731t-38562
8	V94042J-76924 m86742Q-77387 m87671b-82736 b61404H-51106
9	Q74182j-13054 a89937B-55201 _84930a-29203 v48489S-84524
10	`54687L-37310 J89867b-58165 g72969^-48930 M37511]-65751
11	F76605P-58346 T88209q-35721 m38012E-41110 P83160t-54732
12	[70328H-70280 Y80805i-27834 e61064_-59633 M93494Z-24855
13	e77170D-70920 C13090o-84284 j37761U-14190 h66910s-67576
14	A75437Q-52455 p86361_-17299 j60796u-51969 a21507Y-64797
15	]80790G-98676 c97285]-90495 R17310L-66229 N93625Q-64548
16	`72548W-90980 J30881J-52974 b88034x-65181 V77499^-27256
17	b52351K-69098 k91870i-78299 y21525n-18323 v13027H-17546
18	^54335T-36913 S28174^-54282 L20755S-50126 H10028a-49443
19	h87069Q-89702 Q55454L-42935 F22540p-40426 a15579s-37492
20	g29059b-41283 w63654]-80515 L85962t-89073 D80336w-26731
21	]52072Z-10713 f40858M-49689 D47355p-43295 u65097]-75345
22	q11207d-90922 b97088Q-39205 W98030r-84577 q82979q-39837
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="RefNrs_2"]}[Content],
    Type_txt = Table.TransformColumnTypes(Source,{{"InternalRefNum", type text}}),
    Select_q_nrs = List.RemoveNulls(List.TransformMany(Type_txt[InternalRefNum], (x) => Text.Split(x, " "), (x, y) => if Text.StartsWith(y, "q", Comparer.OrdinalIgnoreCase) then y else null ) )
in
    Select_q_nrs

Book1
	E
28	RefNrs_2
29	q88163L-24254
30	Q74182j-13054
31	Q55454L-42935
32	q11207d-90922
33	q82979q-39837
Sheet1