Return string after return character

[RomiToo]

Ok, I have a bunch of address entries, unfortunately the address and city, state, zip are all in one cell and I need to make them 2 or 3

Here's what the data looks like currently

123 Main St. Anytown, AB 11111

123 Main St. Anytown, AB 11111

<tbody>
</tbody>

This is what it needs to look like

123 Main St.

Anytown, AB 11111

123 Main St.

Anytown, AB 11111

<tbody>
</tbody>

This is the formula: =RIGHT(B8,LEN(B8)-FIND("&CHAR(10)",B8)) but I get a #Value error when I try it. What's wrong?

 

[BobUmlas]

change
"&CHAR(10)"
to
CHAR(10)

 

[RomiToo]

Thanks!!!!!