Lesson#99: Formula to separate words from a full name

OK. I am back again with some crazy formula. Here in this post, I will show you how to make a formula to separate words from a full name.

Here in this above picture, I have a list of names who has first name and surname. I will show you how to separate this two.

For the first name use this formula =LEFT(E1,FIND(” “,E1)-1)

This formula will return the position of <SPACE> and take out the characters before it.

And for the last name use this formula =RIGHT(E1,(LEN(E1)-FIND(” “,E1)))

This formula also will return the position of <SPACE> and take out the characters after it.

Now the special cases when you are having a middle name also. We have to make a more complex formula.

For the middle name, we will use this formula.

=IFERROR(LEFT(MID(E1,FIND(” “,E1)+1,100),FIND(” “,MID(E1,FIND(” “,E1)+1,100))-1),””)

See this formula works perfectly.

And for the last name, I had to make a very complex formula and I am sorry for that. 😛

=IF(G1=””,RIGHT($E1,(LEN(E1)-FIND(” “,$E1))),RIGHT(RIGHT($E1,(LEN(E1)-FIND(” “,$E1))),(LEN(RIGHT($E1,(LEN(E1)-FIND(” “,$E1))))-FIND(” “,RIGHT($E1,(LEN(E1)-FIND(” “,$E1)))))))

See, it also works perfectly. Did you like my post? If yes then subscribe to the blog.

Download the file