Yes or No based on Numbers before or after Specific Letter

[mouzzampk]

Hi,

This is my table  Book1.xlsx

Value

ABC1234

BAC5678

123X756

222D897

ADC1234

ADX4325

But how can I show if there are three numbers before or after X or D then say Yes otherwise No.

Value            Comment

ABC1234	No
BAC5678	No
123X756	Yes
222D897	Yes
ADC1234	No
ADX4325	No

[dufoq3 · Accepted solution]

Hi @mouzzampk,

Result:

[ a = Text.SplitAny([Value], "XD"),
  b = List.AnyTrue(List.Transform(a, (x)=> try Number.From(x) is number and Text.Length(x) = 3 otherwise false)),
  c = if b = true then "Yes" else "No"
][c]

[mouzzampk]

This is awesome, thank you. Some next level stuff 🙂

[dufoq3]

If you want to check how every part of record works - remove this [c] at the end and expand the record

[mouzzampk]

You are great, thank you. This is extremely helpful.

[dufoq3]

You're welcome 😉