Parse APT, #, Suite from Address

[MYICON007]

In Excel 2010 need to have the apt number removed from the address 1 and stored in address 2.

EXAMPLE:
"8562 WASHINGTON AVE # 8" needs to be separated into two cells.

I'm using the function below but need to address "APT" and "Suite" also somehow.

=IF(SEARCH("#*";H2));LEFT(H2;SEARCH("#";H2)-1);H2)

gives me: 8562 WASHINGTON AVE

=IF(SEARCH("#*";H2);RIGHT(H2;SEARCH("#*";H2)-I2);"")

gives me: #8

note: I2 = =SEARCH("#";H2)-3

I WANT TO COVER #, APT AND SUITE WITH THE SAME FUNCTION. But when I make it an if or statement like the one below I get a #value! error.

=IF(OR(SEARCH("#*";H2);SEARCH("APT";H2));LEFT(H2;SEARCH("#";H2)-1);H2)

 

[Gerald Higgins]

Maybe this

=IF(SEARCH("#*",H2),LEFT(H2,SEARCH("#",H2)-1),IF(SEARCH("APT",H2),LEFT(H2,SEARCH("APT",H2)-1),H2))

Note that I have used "," as argument separators instead of ";" .