Extract 6-digit number from text if R6 is present in the data

[Mystborn]

I am trying to pull work instruction numbers from my auditors data to determine which are being used the most as rationale for error assignment. This error is only assigned at our "R6" value and should always proceed the rationale in their notes. However, these notes are for all errors assigned to the audit.
This means that I only want the work instruction number that follows after the R6 in their notes. Additionally, this number's placement in the rationales can vary from auditor to auditor.
The number will always be 6 digits, just FYI.

Please see the attached file for the notes with which I am working. I appreciate any help that can be provided. I am not picky if the answer is formulas or VBA.

 

[vletm]

Your the attached file ... is missing.
Create a sample file, which has still Your challenge...

 

[Mystborn]

My file is too big apparently. Will upload in a moment after making it smaller.

 

[Mystborn]

Now smaller

 

[vletm]

Mystborn​

One sample ... press [ Do It ]-button

 

[Peter Bartholomew]

This is strictly for 365 (as is all my work).
To place the results in the table, you will need to concatenate multiple WIs or simple select the first.

= LET(
    followsR6,  TEXTAFTER([@[Constructive_Feedback]], "R6",{1,2,3}),
    rationalle, TEXTBEFORE(followsR6, CHAR(10),,,1),
    wInstrNum,  LEFT(TEXTAFTER(rationalle, "TSRC-PROD-"),  6),
    TEXTJOIN(", ",, IFERROR(wInstrNum, ""))
  )

If you move the calculation outside the table then you can return multiple WIs within an array

= LET(
    rationalle, TEXTBEFORE(TEXTAFTER(Table1[Constructive_Feedback], "R6",{1,2,3,4}), CHAR(10),,,1),
    wInstrNum, LEFT(TEXTAFTER(rationalle, "TSRC-PROD-"),  6),
    IFERROR(wInstrNum, "")
  )

Alternatively you could go straight for an analysis

=LET(
    rationalle, TEXTBEFORE(TEXTAFTER(Table1[Constructive_Feedback], "R6",{1,2,3,4}), CHAR(10),,,1),
    wInstrNum, LEFT(TEXTAFTER(rationalle, "TSRC-PROD-"),  6),
    WIN, TOCOL(VALUE(wInstrNum),3),
    bin, SORT(UNIQUE(WIN)),
    SORT(HSTACK(bin, FREQUENCY(WIN,bin)), 2,-1)
  )

 

[Mystborn]

You all are the best! Thank you for all of the help. ^_^

 

[Mystborn]

This is strictly for 365 (as is all my work).
To place the results in the table, you will need to concatenate multiple WIs or simple select the first.

= LET(
    followsR6,  TEXTAFTER([@[Constructive_Feedback]], "R6",{1,2,3}),
    rationalle, TEXTBEFORE(followsR6, CHAR(10),,,1),
    wInstrNum,  LEFT(TEXTAFTER(rationalle, "TSRC-PROD-"),  6),
    TEXTJOIN(", ",, IFERROR(wInstrNum, ""))
  )

If you move the calculation outside the table then you can return multiple WIs within an array

= LET(
    rationalle, TEXTBEFORE(TEXTAFTER(Table1[Constructive_Feedback], "R6",{1,2,3,4}), CHAR(10),,,1),
    wInstrNum, LEFT(TEXTAFTER(rationalle, "TSRC-PROD-"),  6),
    IFERROR(wInstrNum, "")
  )

Alternatively you could go straight for an analysis

=LET(
    rationalle, TEXTBEFORE(TEXTAFTER(Table1[Constructive_Feedback], "R6",{1,2,3,4}), CHAR(10),,,1),
    wInstrNum, LEFT(TEXTAFTER(rationalle, "TSRC-PROD-"),  6),
    WIN, TOCOL(VALUE(wInstrNum),3),
    bin, SORT(UNIQUE(WIN)),
    SORT(HSTACK(bin, FREQUENCY(WIN,bin)), 2,-1)
  )

View attachment 86651

Peter, how would I change your analysis formula so I could return all work instructions quoted regardless of what service skill is being scored? I'm ok on creating a unique meaningless string like "x123" and adding that to the front of all auditor comments to have an anchor point, so to say, for the analysis formula.

 

[Peter Bartholomew]

I am not sure I have understood this correctly but, if you no longer care about the "R6" code then you could simply list all the work instruction codes.

= LET(
    record, Table1[Constructive_Feedback],
    k,      SEQUENCE(1, 12),
    WINum,  LEFT(TEXTAFTER(record, "TSRC-PROD-",k),  6),
    IFERROR(VALUE(WINum), "")
  )

 

[Mystborn]

I am not sure I have understood this correctly but, if you no longer care about the "R6" code then you could simply list all the work instruction codes.

= LET(
    record, Table1[Constructive_Feedback],
    k,      SEQUENCE(1, 12),
    WINum,  LEFT(TEXTAFTER(record, "TSRC-PROD-",k),  6),
    IFERROR(VALUE(WINum), "")
  )

Thank you!!