c# Regex for address

user_4123372 0
Viewed 73

I have a string from a csv file. I'm trying to remove the commas from values between quotes, but only if it's not a number.

For instance, I have this string.

6/4/2020,11111,John,Doe,"111 st , city,State",city,st,11111,email@gmail.com,11111111111,11111111111,"$370,000.00","$500,000.00 ",blah blah blah,"$370,000.00 ",4.25%,stable,74.00%,Rate and Term,0.00%,$0.00 ,Good,No

The st address from above is

"111 st , city,State"

I can remove the commas between quotes with this regex

var regex = new Regex("\\\"(.*?)\\\"");

However, that also removes the commas in the numbers

"$370,000.00"

How can I remove the commas in the address, but ignore the number fields with a dollar sign $?

Here is an example code

    var test = $"6/4/2020,test,test,testJR,\"111 test DRIVE, city, st\",city,st,11111,test@gmail.com,11111,11111,\"$370,000.00 \",\"$500,000.00 \",Single Family Residence,\"$370,000.00 \",4.25%,Fixed,74.00%,Rate and Term,0.00%,$0.00 ,Good,No";
    var regex = new Regex("\\\"(.*?)\\\"");
    test = regex.Replace(test, m => m.Value.Replace(',', ' '));
regex c#
1 Answers

You may use

var regex = new Regex(@"""\s*\$\d+(?:,\d+)*(?:\.\d+)?\s*""|(""[^""]*"")");
test = regex.Replace(test, m => m.Groups[1].Success ? 
    m.Groups[1].Value.Replace(',', ' ') : m.Value);

See the C# demo

Details

  • "\s*\$\d+(?:,\d+)*(?:\.\d+)?\s*":
    • "\s*\$ - ", then 0+ whitespaces and then a $ char
    • \d+(?:,\d+)*(?:\.\d+)? - 1+ digits, then 0 or more occurrences of a comma and then 1+ digits, and then an optional occurrence of a . and 1+ digits
    • \s*" - 0+ whitespaces and then "
  • | - or
  • ("[^"]*") - Capturing group 1: ", zero or more chars other than " and then ".

The m => m.Groups[1].Success ? m.Groups[1].Value.Replace(',', ' ') : m.Value) means that commas are replaced with spaces only if Capturing group 1 matched, else, the match is returned as is.

user_38329700
Related