Search code examples
regexgoogle-sheetsgoogle-sheets-formularegexp-replace

Extracting the Desired Result from the Data

I have been using below formula to extract the relevant values from the data.

IFERROR(REGEXEXTRACT(A2:A,"TRN*1*\*[^~\\]+"))

Here is the Data.

ENTRY DESCR:HCCLAIMPMTSEC:CCD TRACE#:04858562 TRN*1*6523251452*5865418282584~
ENTRY DESCR:HCCLAIMPMTSEC:CCD TRACE#:04526412 TRN*1*5685952452*158615*6526352 26\ TRN: 563569856

and the output comes like this.

TRN*1*6523251452*5865418282584
TRN*1*5685952452*158615*6526352 26

But i want to extract the output like this

6523251452
5685952452

any help will be appreciated.

Solution

  • You can use

    =IFERROR(REGEXEXTRACT(A2:A,"TRN\*1\d*\*(\d+)"))

    See the regex demo.

    Details:

    • TRN\*1\* - TRN*1* string
    • (\d+) - Group 1 (this value will be the return value of the whole REGEXEXTRACT function): one or more digits.