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.
Leave a Reply