SUBSTITUTE function example

I have a column of codes that have three hyphens eg WT-123-456-789. I need to replace the first hyphen with a space, but leave the other hyphens alone. The first hyphen comes as the third, fourth or fifth character. Is there an easy way to do this?

By Neale Blackwood

The SUBSTITUTE function will allow you to do this. Assuming the code is in cell A1, this formula will replace the first instance of the hyphen with a space.

=SUBSTITUTE(A1,”-“,” “,1)

The 1 at the end of the formula specifies the first instance of the text to be substituted. A 2 would replace the second hyphen with a space and leave the first space. The instance number at the end of the formula could be a cell reference that has an IF function in it to change the instance number based on code length, or some other criteria.

You can use multiple SUBSTITUTE functions to perform more complex replacements. To replace the first and third hyphen with a space you could use the following formula.

=SUBSTITUTE(SUBSTITUTE(A1,”-“,” “,1),”-“,” “,2)

This formula will leave the second hyphen in the code.