Extract characters

Question

I am trying to extract the first two or three characters before "WN" in a text field. Left(Trim([Incident Number]),"WN") works until you have something that has another "N" or "W". Example text is: DTWWN11111111 or DEWNWN000000000. Text can also be CCWN001111111.

I know this might be simple, but I can't figure it out. Any help will be greatly appreciated.

chayceduncan2 · Answer

You could get away using a Part function - which will split your text into parts based on a matching delimiter

So like
Part(string,1,"WN")
Will return whats in front of the first WN

Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base

javiermartinez · Answer

That works for OCWN111111111, but it does not work for DENWN11111111 or DTWWN11111111.

chayceduncan2 · Answer

Strange. Seems to not like using Part or Left. You can probably look at something like:
var text t = "DTWWN11111111";
If(
Right(Left($t,6),2)="WN",Left($t,4),
Right(Left($t,5),2)="WN",Left($t,3),
Right(Left($t,4),2)="WN",Left($t,2),
Right(Left($t,3),2)="WN",Left($t,1),
"")

Basically look at the first 6 characters - if the last two are WN - get everything to the left, if its not WN - look at the first 5, trying to find a WN.

Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base

javiermartinez · Answer

It gives me an error message to check the sytax.

var text t=[Incident Number];
If(
Right(Left($[Incident Number],6),2)="WN",Left($[Incident Number],4),
Right(Left($[Incident Number],5),2)="WN",Left($[Incident Number],3),
Right(Left($[Incident Number],4),2)="WN",Left($[Incident Number],2),
Right(Left($[Incident Number],3),2)="WN",Left($[Incident Number],1),
"")

chayceduncan2 · Answer

You don't need to do $[Incident Number] - you can remove the $ from in front of all those field declarations and just do [Incident Number]. I set up a variable to test a few of your examples - but you don't need itChayce Duncan | Technical Lead(720) 739-1406 |&nbsp;chayceduncan@quandarycg.comQuandary Knowledge Base

Forum Discussion

Extract characters