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
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
------------------------------