Forum Discussion

MikeGerrity2's avatar
MikeGerrity2
Qrew Member
9 years ago

Is there a formula that will tell tell you the position of a specficic character in a text string

I'm looking for the equivalent of the "Find" function in excel.  

find("x","I am x years old") would equal return 6.  Since x is the 6th character in the string.   I don't think this formula exists in quickbase, but any work arounds?

With this formula I can then add logic for left/notleft/right/mid to extract specific text elements.

Data import and export
Formulas and functions

6 Replies

Sort By
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    Try this. I will use formula variables to break the formula into smaller pieces.

    var text Temp = Left([my text field],"x");

    Length($Temp) +1
    • Jan-WillemSpeck's avatar
      Jan-WillemSpeck
      Qrew Assistant Captain
      7 years ago
      I would like to go one step further with this function.

      1) find a specific chararter combination
      ie
      var text Temp = Left([my text field],"#%");
      Length($Temp) +1
      2) from the position where "#%" is located I like to read the next 5 characters
      ie Mid([field], <start position>, 5)

      if I replace <start position> with the two lines in 1) it doesn't work. I assume it has the do with the variable, the code in 1) or with the ;
      Is there a better way to get this?

      In an even better scenarion I should perhaps first check if the text string contains the value "#%"  at all to avoid wrong output of 1). Currently if it can't find it seems to result in the sting length  
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      did you try this

      var text Temp = Left([my text field],"#%");
      var number StartPosition = Length($Temp) +1;

      Mid([field], $StartPosition, 5)
    • Jan-WillemSpeck's avatar
      Jan-WillemSpeck
      Qrew Assistant Captain
      7 years ago
      No I didn't.
      But what I learn from this is how to define and use variables and that you can treat the formula field also as a field where we can enter pieces of code because that is what you actually doing.

      And that opens new possibities, lots of them
      What a joy :-)

      for the second time today
      TXS!!
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    Note that if your goal is to parse out a section of text up to a character, that the Left and Right functions in Quickbase will do that for you.  You can do this

    Left([My string],6)

    But as you can see above, you can also do Left([My String],"x") which gives all the text up to the first x.
    • AdamKrzyzanek's avatar
      AdamKrzyzanek
      Qrew Captain
      5 years ago
      I know it is old Post but Was looking for this solution and did not find in Community.
      Come up with such solutions posted HERE

      ------------------------------
      Adam Krzyzanek
      ------------------------------