Forum Discussion
Separating name
How I separate full name Md. Fakrul Islam into first name Md. Fakrul and last name Islam? Thanks in advance
Hi Shuvra
it depends on your version of Excel. If you use Microsoft 365, the TEXTAFTER function would do it:
=TEXTAFTER(A1," ",2)
It would give the text after the second spcae. If your name contains more than 2 spaces, this one would be more flexible:
=TEXTAFTER(A1," ",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))
It gives the text after the last space.
If you do not use Microsoft 365, this one would help:
=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",)))))
It's more complicated because you need to find the last space in the name. This is the complicated part: the formula calculates the number of spaces by subtracting the length of the name without spaces from the length of the name including spaces.
Hope this helps.