Forum Discussion

JunRea's avatar
JunRea
Copper Contributor
Jun 11, 2022

Extracting first names in a list of names (FIRST NAME MIDDLE INTIAL.)

Good day everyone, I would like to ask if is it possible to get the first names in a list of names with  this kind of format (First Name Middle Initial.). Ex. 1. John Eduard B.                 ...
excel
Formulas and Functions
office 365
JunRea's avatar
JunRea
Copper Contributor
Jun 11, 2022
Problem Solve:
=TRIM(LEFT(SUBSTITUTE(TRIM(A2), " ", REPT(" ", LEN(TRIM(A2)))), LEN(SUBSTITUTE(TRIM(A2), " ", REPT(" ", LEN(TRIM(A2)))))-LEN(TRIM(A2))))
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Jun 11, 2022

    JunRea 

     

    If you have Office 365, you can make it more readable by using the LET function like this...

    =LET(
s,SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),
r,TRIM(RIGHT(s,LEN(A2))),
TRIM(SUBSTITUTE(s,r,""))
)
    • JunRea's avatar
      JunRea
      Copper Contributor
      Jun 11, 2022
      Thank you sir. This formula also works.. May I know what is the meaning of letter "r" and "s" in this formula?

Resources