Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Formula For Text Manipulation

1. Member
Join Date
2nd July 2004
Posts
81

## 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.
Last edited by Keithcornett; October 25th, 2006 at 02:45.

Excel Video Tutorials / Excel Dashboards Reports

2. Long Term Member
Join Date
25th April 2006
Posts
1,195

## Re: Text Manipulation

Excel Video Tutorials / Excel Dashboards Reports

3. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## 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)))

Excel Video Tutorials / Excel Dashboards Reports

4. ## 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)))

5. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## 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

6. Super Moderator
Join Date
7th December 2005
Location
Hampshire, England
Posts
4,898

## 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.

Excel Video Tutorials / Excel Dashboards Reports

7. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## 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
Last edited by Krishnakumar; October 25th, 2006 at 18:16.

8. ## Re: Formula For Text Manipulation

Originally Posted by 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.
Last edited by Dave Hawley; October 25th, 2006 at 18:48.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno