Jan 18 2023 10:27 PM
How I separate full name Md. Fakrul Islam into first name Md. Fakrul and last name Islam?
Thanks in advance
Jan 19 2023 12:00 AM
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.