Forum Discussion

ScottRobertson's avatar
ScottRobertson
Qrew Trainee
6 years ago

Count the number of specific characters in text field

Hello, Is it possible to count the number of specific characters within a text field? What I'm looking to do is count the number of delimiters (";") within a text field to determine the number of e...
Data import and export
Formulas and functions
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
6 years ago
Try this, but it will not work for your last example as there is no text in between the delimiters.

var text value = [my field];

Count(
Trim(Part($value,1,";"))<>"",
Trim(Part($value,2,";"))<>"",
Trim(Part($value,3,";"))<>"",
Trim(Part($value,4,";"))<>"",
Trim(Part($value,5,";"))<>"",
Trim(Part($value,6,";"))<>"",
Trim(Part($value,7,";"))<>"",
Trim(Part($value,8,";"))<>"",
Trim(Part($value,9,";"))<>"",
Trim(Part($value,10,";"))<>"",
Trim(Part($value,11,";"))<>"",
Trim(Part($value,12,";"))<>"",
Trim(Part($value,13,";"))<>"",
Trim(Part($value,14,";"))<>"",
Trim(Part($value,15,";"))<>"",
Trim(Part($value,16,";"))<>"",
Trim(Part($value,17,";"))<>"",
Trim(Part($value,18,";"))<>"",
Trim(Part($value,19,";"))<>"",
Trim(Part($value,20,";"))<>"",
Trim(Part($value,21,";"))<>"",
Trim(Part($value,22,";"))<>"",
Trim(Part($value,23,";"))<>"",
Trim(Part($value,24,";"))<>"",
Trim(Part($value,25,";"))<>"")

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------
  • ScottRobertson's avatar
    ScottRobertson
    Qrew Trainee
    6 years ago
    Appreciate the input!

    ​The other issue I failed to mention is that the length of that field can range from 1 delimiter to 50,000+... would I need to complete 50,000 "trims" with this solution?
    Trim(Part($value,1,";"))<>"",
    Trim(Part($value,2,";"))<>"",
    Trim(Part($value,3,";"))<>"",
    …
    Trim(Part($value,50000,";"))<>"",


    ------------------------------
    Scott Robertson
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      6 years ago
      Yes, 50,000, which I'm sure would hit either Quick Base limits or limits of your patience.  This would require a JavaScript solution.

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      markshnier2@gmail.com
      ------------------------------
      • ScottRobertson's avatar
        ScottRobertson
        Qrew Trainee
        6 years ago

        Thanks Mark, that's what I was afraid of.

         

        This information is coming in through an API so I'm going to explore obtaining the counts before posting to QuickBase.

         

         

        Thanks again,
        Scott


        Confidentiality Notice:
        This e-mail, including any attachments is the property of Trinity Health and is intended for the sole use of the intended recipient(s). It may contain information that is privileged and confidential.  Any unauthorized review, use, disclosure, or distribution is prohibited. If you are not the intended recipient, please delete this message, and reply to the sender regarding the error in a separate email.


        ------Original Message------

        Yes, 50,000, which I'm sure would hit either Quick Base limits or limits of your patience.  This would require a JavaScript solution.

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        markshnier2@gmail.com
        ------------------------------
    • DonLarson's avatar
      DonLarson
      Qrew Elite
      6 years ago
      Scott,

      If you have an external data source with the delimited text, you should transform the data before writing to Quick Base.  What ever code you are using to extract from the other DB and load to Quick Base is undoubtedly more powerful than the formula fields in Quick Base.  Quick Base is great but is deliberately kept simple for the Citizen Builder to be able to make business process applications for users. 

      Your case above is outside the box for Quick Base but not that difficult with a Loop in your external code.   Then you can add a second field in Quick Base that is the Count of Delimiters as a Numeric field instead of a Formula Numeric.

      I assume with the text field and numeric field you can then get on to the business case for why you wanted to import the data to begin with.

      Good luck and happy building.

      Don


      ------------------------------
      Don Larson
      Paasporter
      Westlake OH
      ------------------------------