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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]123 Main St.
Anytown, AB 11111[/TD]
[/TR]
[TR]
[TD]123 Main St.
Anytown, AB 11111[/TD]
[/TR]
</tbody>[/TABLE]

This is what it needs to look like
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]123 Main St.[/TD]
[/TR]
[TR]
[TD]Anytown, AB 11111[/TD]
[/TR]
[TR]
[TD]123 Main St.[/TD]
[/TR]
[TR]
[TD]Anytown, AB 11111[/TD]
[/TR]
</tbody>[/TABLE]

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!!!!!