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

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
See also  Lesson#110: Separate characters from the text where any word appears

Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

Leave a Reply

Your email address will not be published. Required fields are marked *

*