Excel substitute function not working as expected

Question

Data in cell A1 is (16) UMBC vs. (16) Howard

Function in cell A2 is: Substitute(A1," ","|",4)

Function returned (16) UMBC vs. (16) Howard - exactly what was in cell A1

It did not replace the 4th space with the | as expected: (16) UMBC vs. (16)|Howard.

Anyone know what I'm doing wrong?

Answer 1

Try this:
=SUBSTITUTE(TRIM(A1)," ","|",4)

Let me know if this is the result you are expecting?

Answer 2

SUBSTITUTE counts every character that Excel recognizes as a space, including non‑breaking spaces (often pasted from web pages), but the instance_num argument refers to occurrences of the exact old_text string.

In this case, the “spaces” in the text are not all the same character. Some are likely regular spaces, and at least one is a non‑breaking space (CHAR(160)), so " " is not matching the fourth separator as expected.

To fix it, first normalize all non‑breaking spaces to regular spaces, then apply SUBSTITUTE. For example:

=SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," ")," ","|",4)

Explanation:

1. SUBSTITUTE(A1,CHAR(160)," ") converts all non‑breaking spaces to normal spaces.
2. The outer SUBSTITUTE(...," ","|",4) then correctly replaces the 4th regular space with |.

If needed, adjust the instance_num (the 4th argument) to target a different occurrence.

For reference, SUBSTITUTE syntax is:

SUBSTITUTE(text, old_text, new_text, [instance_num])

Where instance_num is optional; if omitted, all occurrences of old_text are replaced.

---

References:

• SUBSTITUTE function