SEARCH FOR LETTER "A" AND "B" IN A TEXT AND PUT 1 ELSE 0 IN NEW COLUMN

[Mekan]

I have a problem I have a strings something like below patterns:

A12345B, 12355, C56463D, A64631D,...

I want to create a simple column where I want to search for string that starts with A and ends with B and put 1 else 0? I would appreciate your help! I tried to use search and wildcards no luck! 

NEWCOLUMN=IF(SEARCH("A*B",TABLE[STRING],1,BLANK())>0,1,0)

[v-yangliu-msft]

Hi  @Mekan ,

Here are the steps you can follow：

1. Create calculated column.

NEWCOLUMN =
var _A=IFERROR(
SEARCH("A",[ID]),-1)
var _B=IFERROR(
SEARCH("B",'Table'[ID]),-1)
var _len=LEN('Table'[ID])
return
IF(
    _A=1&&_B=_len,1,0)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mahoneypat · Accepted solution]

Here is one way to do it, that looks for A and B anywhere in the string.  

Has A and B =
VAR searchtable =
    FILTER ( { "A", "B" }, SEARCH ( [Value], 'Table'[String], 1, 0 ) > 0 )
RETURN
    IF ( COUNTROWS ( searchtable ) = 2, 1, 0 )

If you need the match first/last, you can use this.

A Start B End = IF(Left('Table'[String], 1) = "A" && RIGHT('Table'[String], 1) = "B", 1, 0)

Pat

[Mekan]

Excellent, this is exactly what I wanted! I used your first/last solution and it worked perfectly! Thanks a lot for sharing! Have a great day!