Isn't there an argument you can supply to the REPLACE function that does it to all occurrences, not just the first one? Let's see.... Nope, guess I just dreamt that. No, wait a minute, here it is; use SUBSTITUTE instead.
SUBSTITUTE(text, old_text, new_text, [instance_num])The SUBSTITUTE function syntax has the following arguments:
- Text Required. The text or the reference to a cell containing text for which you want to substitute characters.
- Old_text Required. The text you want to replace.
- New_text Required. The text you want to replace old_text with.
- Instance_num Optional. Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.