Forum Discussion

ThomasRingkamp1's avatar
ThomasRingkamp1
Qrew Member
3 years ago

Parsing a Text string and extracting values

I am trying to Parse a Text string from a connected database to extract Location and Amount data. The string looks like this:

'FREMONT', null, ('157', null, null, null), null, null, null, 5634.88, null, null, null, 18050.0, null, 0.0, 18050.0, 0.0, null, null, null, null, null, null, null, null), (null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 'HAGERSTOWN', null, ('109', null, null, null), null, null, null, 5011.86, null, null, null, 20934.0, null, 0.0, 20934.0, null, null, null, null, null, null, null, null, null), (null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 'OPELIKA', null, ('112', null, null, null), null, null, null, 16224.42, null, null, null, 54332.0, null, 0.0, 54332.0, 0.0, null, null, null, null, null, null, null, null), (null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 

Is there any way of doing this?



------------------------------
Thomas Ringkamp
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    what is the result that you want.
    Mark

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • ThomasRingkamp1's avatar
      ThomasRingkamp1
      Qrew Member
      3 years ago
      Sorry, guess that would help. I would like a record with 3 fields for Location then the first number and the second. So for example:
      Fremont - 5634.88 - 18050.0
      Hagerstown - 5011.86 - 20934.0

      ------------------------------
      Thomas Ringkamp
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        3 years ago
        this seemed to work to strip out the extra text.
        var text RemoveNulls = SearchAndReplace([Input String], "null,","");
        var text RemoveOneBracket = SearchAndReplace($RemoveNulls, "(","");
        var text RemoveOtherBracket = SearchAndReplace($RemoveOneBracket, ")","");
        var text RemoveApostrophe = SearchAndReplace($RemoveOtherBracket, "'","");
        var text RemoveNullAgain = SearchAndReplace($RemoveApostrophe, "null,","");
        var text RemoveZeroPointZero = SearchAndReplace($RemoveNullAgain, " 0.0,","");
        var text RemoveSpaces = SearchAndReplace($RemoveZeroPointZero, " ","");

        $RemoveSpaces

        Then you can use the Part function to pull out various of the parts separated by a comma into separate fields.

        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------