Help with RIGHT function

[Yusuf]

Hi

I'm having difficulty understanding why my LEFT and RIGHT functions aren't doing what I want.

I have a column with addresses and I'm trying to split up certain parts into different columns to mail merge.

Eg in cell A1;
25 Speed Lane Edinburgh Scotland

If I use in B1 =LEFT(A1,FIND(" ",A1,1))
I get the number of the house, which is what I'm looking for.

If I try to get the last part, ie "Scotland"
Eg. =RIGHT(A1,FIND(" ",A1,1))
I don't get the full name but the last two letters only.

In some cells I return three letters of the last town name


Please help with a solution.

 

[Oaktree]

Try:

=REPLACE(A1,1,SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),"")

Breaking down =RIGHT(A1,FIND(" ",A1,1)) , you were finding the first space, starting with the first character. That was returning 3, as the space is the third character. So, =RIGHT(A1,FIND(" ",A1,1)) was returning the 3 right most characters (I'm guessing you had a trailing space at the end...)