Forum Discussion

JavierMartinez's avatar
JavierMartinez
Qrew Cadet
6 years ago

Extract characters

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.
    • JavierMartinez's avatar
      JavierMartinez
      Qrew Cadet
      That works for OCWN111111111, but it does not work for DENWN11111111 or DTWWN11111111.
    • ChayceDuncan2's avatar
      ChayceDuncan2
      Qrew Cadet
      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's avatar
      JavierMartinez
      Qrew Cadet
      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),
      "")
  • This tested OK for me

    var text ReplaceWNwithPipe = SearchAndReplace([data field],"WN","|");

    Left($ReplaceWNwithPipe,"|")
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      Try this

      var text ReplaceWNwithPipe = SearchAndReplace(Upper([data field]),"WN","|");

      Left($ReplaceWNwithPipe,"|")