Announcement

Collapse
No announcement yet.

Find and remove all data to the right of comma

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

  • Find and remove all data to the right of comma



    I have several columns of addresses in varying formats. I want to find and remove all data to the RIGHT of the first comma in the string of text. Any suggestions on ways to do this? I've tried Excel's Find/Replace, but couldn't find comma nor delete all charactors following the comma.

    I also tried some formulas like:
    =MID(SUBSTITUTE(A2," ",""), FIND(",",SUBSTITUTE(A2," ",""))+1,2)

    but that didn't work well.

    The following are two examples of cells in a column. I want to strip out all the information right of the comma.

    Sacramento, CA 95815-3514
    Los Angeles, CA 90025


    Thank you for your ideas and suggestions.

  • #2
    Re: Find and remove all data to the right of comma

    How about
    =TRIM(SUBSTITUTE(A1,MID(A1,FIND(",",A1),256),""))

    Or, use Data>Text to columns..

    Comment


    • #3
      Re: Find and remove all data to the right of comma

      Hi,

      =MID(A1,1,FIND(",",A1)-1)

      Or Hit Ctrl+H

      Find what: ,*
      Click on Replace All

      HTH
      Kris

      ExcelFox

      Comment


      • #4
        Re: Find and remove all data to the right of comma

        one formula based approach

        =LEFT(A1,FIND(",",A1)-1)

        Comment


        • #5


          Re: Find and remove all data to the right of comma

          Thank you all for your ideas and suggestions.

          The simpliest way is, in this case, is the best.

          Or Hit Ctrl+H

          Find what: ,*
          Click on Replace All


          This method is as flexible as I would like, but worked for this case.

          Thank you.

          Comment

          Working...
          X