CODEWITHSUNDEEP
excel-formula
Scottyp
Scottyp

Reputation: 111

How to extract Middle Names using formula

I am trying to:

  1. extract clients' middle name(s) from a field; or
  2. delete the first and last name, leaving the middle name(s)

but I am stuck. I can remove the last word using 

=LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)

I can remove the first word by

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

But I can't combine them. I have looked online and found this:

=TRIM(REPLACE(SUBSTITUTE((TRIM(REPLACE(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),0*LEN(A1)+1,LEN(A1),"")))," ",REPT(" ",LEN(A1))),(1+LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)*LEN(A1)+1,LEN(A1),""))

But it doesn't work.

Upvotes: 1

Views: 304

Answers (1)

p._phidot_
p._phidot_

Reputation: 1950

Just put the ' remove the last word ' formula and use it in the ' remove the first word ' formula .

Implementation : Just replace all A1 in the ' remove the first word ' formula with ' remove the last word ' formula . 

=RIGHT(LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1),LEN(LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1))-FIND(" ",LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)))

Done.

Hope it helps.

Upvotes: 1

Related Questions