Right, Len & Substitute Functions

[Dominique Feteau]

I have a sheet with a column that has address values, but the suite,
building or room number is before the actual address (e.g. Suite 1, 123 W.
Main St.)

i'm using this formula to get "123 W. Main St.":
=RIGHT(B2,LEN(B2)-FIND(", ",SUBSTITUTE(B2,"
",",",LEN(B2)-LEN(SUBSTITUTE(B2," ","")))))

the problem with it is that in the new cell, there is an extra space in the
beginning.

When I try to get the "Suite 1" using: =LEFT(C14,FIND(",",C14,1)), I get the
comma at the end.

Can anyone help me out?

 

[Peo Sjoblom]

Just subtract

=LEFT(C14,FIND(",",C14,1)-1)