Regex - Searching for a string after nth comma in CSV file

Question

I inherited a project at the office that I can't quite seem to find the answer to. I am trying to write a Powershell script using Regex to replace text in specific field of a multi-line comma separated CSV file, but the text I am replacing could exist in other fields.

For example, in the following data:

Smith,Robert,W,11111 N 400 W,Some City,Some State,Some Zip

I need to replace W in the fourth column with West, but I don't want the W in the third column (or other columns) to be changed. At the very least, I could just have it search for any match after the nth comma since the fields following the street address are mostly numeric, but ideally, it'd be nice to only search the specific column in case there would be matching text elsewhere in the future.

I have found some tips and Regex scripts that seem to get me close, but am struggling get it to work testing it with Notepad++. Some scripts are searching everything up to the nth comma, some seemed to be searching backwards from the end of the line (which won't work because there are text fields with commas). The closest I have gotten in the PowerShell script so far is:

-replace('^(?:[^,]*\,){3}([^,]*)\bW\b', 'West')

But this replaces the W and everything before it with West.

It's likely that there is even a better way to do this, but I've already spent more time on this than I anticipated. Any help would be greatly appreciated!

Answer 1

Fairly straightforward. The regex you tried was very close.
Leading up to the W in the fourth column just has to be written back along with West
in the replacement.

This is a portable solution.

@"(?m)^((?:[^,]*,){3}[^,]*?)\bW\b"

Replace $1West

https://regex101.com/r/zLlP7L/1

(?m)
^
(                             # (1 start)
   (?: [^,]* , ){3}
   [^,]*? 
)                             # (1 end)
\b W \b

Answer 2

Using Notepad++:

• Ctrl+H
• Find what: ^(?:[^,]*\,){3}[^,]*?\KW
• Replace with: West
• TICK Match case
• TICK Wrap around
• SELECT Regular expression
• Replace all

Explanation:

^           # beginning of line
(?:         # non capture group
    [^,]*       # 0 or more any character that is not a comma
    ,           # a comma
){3}        # end group, must appear 3 times
[^,]*?      # 0 or more any character that is not a comma, not greedy
\K          # forget all we have seen until this position
W           # letter W

Screenshot (before):

Screenshot (after):