Find & Replace All Occurances After The First One In A String

In Excel, I often have lists like this:

1815 Juniper Street - Mary - Janice - Ming
1754 Maple street - Richard - William
445 8th Avenue, Richmond - Dana - Stephanie - Gloria - Andrea

The format is basically Address - Name - Name

I'm trying to build a macro that would allow me to select a few cells on my worksheet, and reformat the strings so they look like this:

1815 Juniper Street - Mary, Janice, Ming
1754 Maple street - Richard, William
445 8th Avenue, Richmond - Dana, Stephanie, Gloria, Andrea

I want the format to be Address - Name, Name. That means I want to leave the first occurrence of the dash, but find every subsequent occurrence of the dash and replace it with a comma.

I have some basic macro experience, and I have a feeling that Instr will be involved here, but I don't really know how to do it. Any suggestions, links or ideas would be appreciated.

Thanks,
k8

Hi,
Thanks, watersev, for posting this. When I run the macro, it replaces the correct dashes with commas, but the corrected cells are pasted one column over to the right from the original, and always in starting at row 1. I would like the changes to occur in place. From what you supplied, I can't figure out why this is happening or how to fix it. Can anybody help?

Here's what watersev posted.

Please Login or Register  to view this content.