Extracting the Desired Result from the Data

Admin 1
Viewed 64

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.

regexp-replace google-sheets-formula google-sheets regex
1 Answers

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.
user_38329700
Related