The data I am working with lists the street name & unit number two different
ways.

Sometimes it looks like this:

MAIN ST#34

and sometimes it looks like this"

MAIN ST #34 (with the unit number in the next column)

What code could I use to test for the existence of '#' in a cell and if it
exists, separate them so the street name stays in the same cell and the unit
number, including the '#' is placed in the next column of the same row?

Thanks for any help or suggestions on this.

Ken Loomis