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 whic...
Forum Discussion
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
------------------------------
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
------------------------------
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
------------------------------Thanks Mike, that code makes sense and seems to be working a charm.
Much appreciated.
Joe
------------------------------
Joe Alderson
------------------------------