Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Formula For Text Manipulation

  1. #1
    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. #2
    Join Date
    25th April 2006
    Posts
    1,195

  3. #3
    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. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    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. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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. #6
    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. #7
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Krishnakumar; October 25th, 2006 at 18:16.

  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Formula For Text Manipulation

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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Sort Formula Results Where Formula Returns Blank Empty Text
    By Demons_traitor in forum Excel General
    Replies: 1
    Last Post: April 17th, 2008, 18:55
  2. Simple text manipulation Macro
    By Jeffb108 in forum Excel General
    Replies: 11
    Last Post: March 13th, 2005, 10:54
  3. text manipulation
    By Tiger1vic in forum Excel General
    Replies: 4
    Last Post: December 11th, 2004, 05:42
  4. [Solved] Excel Text Manipulation
    By scojax22 in forum Excel General
    Replies: 4
    Last Post: October 31st, 2003, 00:26

Bookmarks

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