Remove consecutive spaces and replace with single comma

Have a list of names. For a majority of names the cell will contain the persons last name; followed by 2 or more spaces; followed by the first name. What I would like to accomplish is to remove in case in which there are two or more consecutive spaces and replace all with a comma and single space. Example of output below

original data "ANDERSON MICHAEL"

replace with "ANDERSON, MICHAEL"

Is this possible with a formula or do I need to use VBA. I have attached a small sample in column A and the desired results in column B.

Thanks

It is close to what I need but not quite there. For example, it is changing the following:

Original data: "ANDERSON JR PATRICK"
Updated data: "ANDERSON, JR PATRICK"
Desired data: "ANDERSON JR, PATRICK"

I only need to replace if there is a string of 2 or more consecutive spaces. I also have the following in the list

Original data: "JOHN G ANDERSON FAMILY TRUST"
Desired data: no change as there is no occurrence of 2 consecutive spaces.

Thanks

I didn't see any protect status on the first workbook attached. Was there a password on the first one? I'm reattaching per your comment. Thanks

You put me on the correct path with the SUBSTITUTE function; which I don't use it too often. I was able to modify formula using an if statement to figure out where the 2 consecutive spaces started in the string. Add in an IFERROR function; gets me to the end result. Thanks

Please Login or Register  to view this content.