Find in string BE followed by 2 numbers?

[mdex]

Hi,

I need to highlight all rows that have the letters BE followed by 2 numbers in a long text field. Is this possible?

Thanks in advance

[amitchandak]

@mdex , a new measure

If(left(max(Table[Column]),2)  = "BE" && mid( max(Table[Column]),3,1) >= '0' &&  mid( max(Table[Column]),3,1) <= '9' && mid( max(Table[Column]),4,1) >= '0' &&  mid( max(Table[Column]),4,1) <= '9', "Green", "red")

And use this  conditional formatting using the filed value How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

[mdex]

Hi,

Thanks for helping. This is very close but seems to be expecting "BE" to be at the start of the field, it could actually be anywhere.

E.g. "TEST REVIEW BE22 Incident"

[tex628]

Hi @mdex, 

I would proberbly use the Split on delimiter or extract on delimiter functions in Power Query to identify if there is a "BE" present in the string column. Then with your new column from the split you can then use a LEFT( Table[Column] , 2) to identify the two numbers. 

Br, 
Johannes

[mdex]

If BE appears in a word before the BE22 then that would cause problems?

"Been to visit BE22"

[tex628]

Hi! 

You are very correct. I've created a new power query column that you can try! 

List.Transform(
List.RemoveItems(
List.Transform(
Text.Split([Column1] , "BE"),
each try
Number.FromText(Text.End(Text.Start("BE" & _,4),2)) otherwise null 
), {null}
), each "BE" & Number.ToText(_))

It might be a good idea to uppercase the column first. 

I've also attached a pbix to this message. 

/ J

[mdex]

Really appreciate the assistance tex and sorry for coming back so late.

This method doesn't quite work either. Using your example pbix it identifies some incorrect BE## combinations.

[tex628 · Accepted solution]

Ohh, 

Try this: 

List.Transform(
List.RemoveItems(
List.Transform(
List.RemoveFirstN(
Text.Split([Column1] , "BE"),
1),
each try
Number.FromText(Text.End(Text.Start("BE" & _,4),2)) otherwise null 
), {null}
), each "BE" & Number.ToText(_))

[mdex]

Thanks so much tex, Now I'll research those functions and find out what they actually do!

[tex628]

No worries! Let me know if there is anything you're wondering about 🙂