Text string function

[arsenal1983]

Hello,

I have column with partner ID and I would like to flag in my script partners with prefix ZD and the numer on the third place (ZD0, ZD3, ZD9 but without ZDQ).

any suggestions?

Luke

[ramoncova06]

maybe something like

if(left(partner ID,3) <> 'ZDQ' and left(partner ID,2) = 'ZD', 'Yes','No') as ZDFlag

[swuehl]

If( Left( ID, 2) = 'ZD' and isnum(mid( ID, 3,1)), 'Yes','No') as Flag

[maxgro · Accepted solution]

if(left([partner ID],2) = 'ZD' and match(right([partner ID],1),1,2,3,4,5,6,7,8,9,0), 'Yes','No') as flag

[rwunderlich]

Just because I've always wanted to find a use for the "precedes" operator:

=TextBetween([Partner ID],'ZD','') precedes 'A'

-Rob

http://masterssummit.com

http://robwunderlich.com

[rbecher]

..but watch out, help means precedes operator is using ASCII Comparison, but it's not:

= '9' precedes ':'    => false

but

= ord('9') < ord(':')  => true

So, my guessing is some other internal order is used.

[MarcoWedel]

another one:

IsNum(Num#(partnerID,'ZD0'))

hope this helps

regards

Marco

[MarcoWedel]

[swuehl]

Unfortunately, this also accepts codes with second char being any number like:

=isNum(Num#('Z17','ZD0')) 

Probably because 'D' is a format code coincidentially.

[MarcoWedel]

'D1' also is flagged.

Maybe not the best solution after all

thanks

Marco