1

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:

enter image description here

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

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

result:

enter image description here

1 Answer 1

2

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.
2
  • Wow David. That worked. I swear I tried numerous combinations but none worked. Thank you so much!
    – Genny
    Jun 25 at 13:18
  • Your welcome, @Genny !
    – David Tan
    Jun 25 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.