Forum Discussion

Shuvra's avatar
Shuvra
Copper Contributor
Jan 18, 2023

Separating name

How I separate full name Md. Fakrul Islam into first name Md. Fakrul and last name Islam?

 

Thanks in advance 

excel

1 Reply

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Jan 19, 2023

    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.

     

Resources