Regexextract to get a text in the middle of string

Question

SAMPLE SHEET

I have data in a column in this format:

1 P01 - Product Name here - PAID IN FULL

2 P02 - Product2 Name here - PARTIALLY PAID 20

1 P03 - Product2 Name here

I've been trying to extract just the middle part, i.e. the code & product name, using regex. This bit P03 - Product2 Name here The dash between the code & name should be left as is.

I managed to remove the first bit, 1.... etc, but I can't seem to get rid of the last bit - PAID IN FULL etc. This last dash should also go.

Using Regexreplace

=ARRAYFORMULA(TRIM(REGEXREPLACE(A2:A3, "^(\d)\s", "")))

result: P01 - Product Name here - PAID IN FULL

Regexextract

=arrayformula(REGEXEXTRACT(A2:A3, "[^(\d)\s].*"))

result: P01 - Product Name here - PAID IN FULL

=arrayformula(REGEXEXTRACT(A2:A3, "[^(\d)\s+].*[^-A-Z$]"))

result: P01 - Product Name here - PAID IN

Extract & replace

=arrayformula(REGEXEXTRACT(A2:A4, "[^(\d)\s+].*[^-$]"))

result:

Same above formula edited a bit to remove ^ before - :

=arrayformula(REGEXEXTRACT(A2:A4, "[^(\d)\s+].***[-$]**")) 

result:

Answer 1

Using a capturing group, we can match a pattern then choose which part of the pattern we want to return.

=ArrayFormula(REGEXEXTRACT(A2:A4,"^\d\s+(\w\d+\s+-\s+[A-z 0-9]+)"))

Notes:

1. I'm using \s+ since I noticed that the spaces can be a variable length.
2. ^\d\s+ This is the beginning of the text. I have a feeling this won't change significantly.
3. \w\d+ is the P01. If your product code can include many leading letters, change it to \w+\d+.
4. \s+-\s+ is just the dash, including the spaces around.
5. [A-z 0-9]+ is just a char set that includes all alphanumeric characters and spaces. Simply adjust this if your product names aren't included here. Dashes in product names will not work for this pattern.