Remove specific words from csv line that contain specific word

Question

I have meta keywords exported in a csv file

Ex:

For Rent ABC-1234 , For Rent ABC-1234 Rental, For Rent ABC1234 , For Rent ABC1234 Rental, For Rent ABC 1234 , For Rent ABC 1234 Rental, For Lease ABC-1234 , For Lease ABC-1234 Rental, For Lease ABC1234 , For Lease ABC1234 Rental, For Lease ABC 1234 , For Lease ABC 1234 Rental

What I would like to do is for the values that read "For Rent XXX Rental" to remove the "For Rent" from those values, so that The remaining value would just read "XXX Rental".

Is that possible to do with regex?

Answer 1

I think this is what you want:

(A[^,]+\d Rental)

This will pick out "xxx Rental" from "For Rent/Lease xxx Rental", and you don't need to replace anything.

EDIT:

The above works if the i (case-insensitive) flag is not on.

For case insensitive regex, use the following instead:

(?<=\w{4}\s|\w{5}\s)([^,]+\d Rental)

• (?<=\w{4}\s|\w{5}\s) is a positive look behind pattern that matches strings followed by \w{4}\s (Rent) or \w{5}\s (Lease).

---

For \w+ \K([^,]+\d Rental)

• \K discards all previous matches and starts matching from current position.

Answer 2

You can use this regex in Find what field:

For Rent ([^,]+ Rental)

Replace with

$1

[^,] means "any character but a comma, + - 1 or more times. I see the values are comma-separated, so this seems a safe pattern. For even safer matching, you can try For Rent ([^,]*? +Rental) in the Find what field (this will also account for multiple spaces before Rental).

Output:

For Rent ABC-1234 , ABC-1234 Rental, For Rent ABC1234 , ABC1234 Rental, For Rent ABC 1234 , ABC 1234 Rental, For Lease ABC-1234 , For Lease ABC-1234 Rental, For Lease ABC1234 , For Lease ABC1234 Rental, For Lease ABC 1234 , For Lease ABC 1234 Rental