I have a google sheet where the contents of a particular cell is a comma separated list of names. The names are enclosed in double quotes but the names have commas in them as they are formatted as LastName Comma Space FirstName Space MiddleInitialIfGiven.
Looks like:
"Gilbert, Matthew C", "Wisters, Helena A", "Wisters, Michael", "Clark, Jim A", "Gilbert, Matthew C", etc (yes, there are duplicates in the cell). What formula(s) would I use to strip the commas WITHIN the text strings, but leave the comma delimiters? regexreplace() strips out all the commas in the cell.
The cell purposely has a list instead of a single value as it was pulled from another dataset. My end goal is getting the duplicates out of this list but for now I just want the commas inside the text strings gone. Unless it’s dumb to approach it that way; if I don’t have to take the commas out to get a list of unique names I’d rather do that and skip the extra step (but i am extremely curious as to how I would get the commas out. Google searches have come up empty for me on this).
>Solution :
Here’s one way:
=REGEXREPLACE(A1,",( \w)","$1")
Explanation
,( \w) – captures all the occurrences of Space followed by a word character \w if preceded by a comma ,
$1 – replaces everything , \w with the captured group \w.