 # Formula For Text Manipulation

• I have a file with a physician name and title in the cell as follows:

"James A. Jones, M.D."

I need a formula to reverse the last name, drop the title "M.D.", get rid of any additional periods after the middle initial and put it in upper case:

"JONES, JAMES A"

There are some names without middle initials also so this may present an additional problem.

Thanks for any help on this.

• Re: Text Manipulation

Try:

=UPPER(IF(FIND("M.D.",A1)<FIND(".",A1),MID(A1,FIND(" ",A1)+1,FIND("M.D.",A1)-FIND(" ",A1)-1)&LEFT(A1,FIND(" ",A1)),MID(A1,FIND(".",A1)+2,FIND("M.D.",A1)-FIND(" ",A1)-4)&LEFT(A1,FIND(".",A1)-1)))

• Re: Text Manipulation

Try

=TRIM(UPPER(SUBSTITUTE(MID(A1,FIND(".",A1)+2,256),"M.D","") & " " & LEFT(A1,FIND(" ",A1))& MID(A1,FIND(" ",A1)+1,1)))

• Re: Text Manipulation

Hi,

=UPPER(REPLACE(SUBSTITUTE(A1," M.D.",""),1,LOOKUP(9.9999999E+307,FIND(" ",SUBSTITUTE(A1," M.D.",""),ROW(INDEX(A:A,1):INDEX(A:A,LEN(SUBSTITUTE(A1," M.D.","")))))),"")&" "&LEFT(A1,LOOKUP(9.9999999E+307,FIND(" ",SUBSTITUTE(A1," M.D.",""),ROW(INDEX(A:A,1):INDEX(A:A,LEN(SUBSTITUTE(A1," M.D.",""))))))-1))

HTH

• Re: Formula For Text Manipulation

Dave, your formula does not give correct results, with or without a middle initial.

For James A. Jones, M.D. it gives "JONES, . JAMES A"

For James Jones, M.D. it gives ". JAMES J"

Krish, your formula works if there is no middle initial, but not if there is. It gives a result with "JONES," then two strange characters, then "JAMES A." - note the full-stop is wrong.

• Re: Formula For Text Manipulation

Hi,

Here is a revised one.

Select A2

Define Ref

Refers to:

=LOOKUP(9.9999999E+307,FIND(" ",SUBSTITUTE(Sheet1!\$A2," M.D.",""),ROW(INDEX(Sheet1!\$A:\$A,1):INDEX(Sheet1!\$A:\$A,LEN(SUBSTITUTE(Sheet1!\$A2," M.D.",""))))))

In B2,

=UPPER(REPLACE(SUBSTITUTE(A2," M.D.",""),1,Ref,"")&" "&SUBSTITUTE(LEFT(A2,Ref-1),".",""))

HTH

• Re: Formula For Text Manipulation

Quote from ByTheCringe2

Dave, your formula does not give correct results, with or without a middle initial.

Yes, only works on the sample as shown. If there is a full stop after M.D then use "M.D." in the subtitute function.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!