I don't think Vito is coming back after 3 years.

How about:
=SUBSTITUTE(A1,"|","New Text")

Or if you are trying to spread the data to various columns, splitting
it up at the vertical line, just use Text To Columns, Delimited, with
| as the delimiter.