Forum Discussion

JoeAlderson's avatar
JoeAlderson
Qrew Member
2 years ago

Looking to extract data from a text field

Hi all,

I'm looking for a way to extract text from the middle of a text field where there's a match to data in another field. As an example say I have a record with a field for Tax Year Ending which will be 4 digits i.e. 2021 or 2022 etc.

I need to use that field to find matches in another field which will have an ID number followed by a / and then a tax year, but it can have more than one of these.

For example if this other field (let's call it Summary) contains the following:

"16/2023 ; 9/2022"

And the Tax Year Ending in the record is 2022, I need another field to extract "9" from the Summary. If the Tax Year Ending was 2023 then it would need to extract "16" from the Summary.

If it's easier to do then it could extract "9/2022" or "16/2023" respectively but it would be cleaner to skip that step if possible.

Many Thanks,

Joe



------------------------------
Joe Alderson
------------------------------

  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    2 years ago

    Can we assume the Summary field will only contain one instance of each year? Meaning, this will never occur: "16/2023 ; 9/2023; 19/2022"

    Where we have two instances of 2023?

    And what are the max number of parts in the summary field? Will you ever have 50 or 60 parts? Or always say, less than 15?

    ------------------------------
    Mike Tamoush
    ------------------------------

    • JoeAlderson's avatar
      JoeAlderson
      Qrew Member
      2 years ago

      Hi Mike,

      Yes that's correct, there would only ever be one instance of a tax year in the Summary field, so you would never see the same tax year twice.

      I could adjust the data in the Summary field to read 2022/9/2022 ; 2023/16/2023. Would that make it easier to extract the text between the tax year?

      A safe cap would be 30 parts in the Summary field maximum, though it's unlikely to reach that. Probably in practice 20 would be the maximum we would see.



      ------------------------------
      Joe Alderson
      ------------------------------

      • MikeTamoush's avatar
        MikeTamoush
        Qrew Commander
        2 years ago

        I would use the Part function, as well as some Left function. Would be a long code, but repetitive so not too bad. 

        So your field that is extracting your 9 or 16 would look like this

        var text TaxYear = [Tax Year Field];

        var text SummaryField = ToText([The Summary Field]); //this only needs the ToText if it is a combined text field or something other than text

        var text PartOneSummary = Trim(Part($Summary, 1, ";" ));
        var text PartTwoSummary = Trim(Part($Summary, 2, ";" ));
        .
        .
        var text PartThirtySummary = Trim(Part($Summary, 30, ";" ));

        If(
        contains($PartOneSummary, $TaxYear), Left(PartOneSummary, "/"),
        contains($PartTwoSummary, $TaxYear), Left(PartTwoSummary, "/"),
        .
        .
        contains($PartThirtySummary, $TaxYear), Left(PartThirtySummary, "/")
        )



        ------------------------------
        Mike Tamoush
        ------------------------------