Announcement

Collapse
No announcement yet.

Formula For Text Manipulation

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 02:45.

  • #2
    Re: Text Manipulation

    heres another

    http://www.ozgrid.com/Excel/TextFormulas.htm

    Comment


    • #3
      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)))
      .

      Comment


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

        Comment


        • #5
          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
          Kris

          ExcelFox

          Comment


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

            Comment


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

              ExcelFox

              Comment


              • #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, 18:48.

                Comment

                Working...
                X