Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

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

  1. #1
    Join Date
    12th March 2003
    Posts
    66

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,709

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

  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,328

    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

  4. #4
    Join Date
    26th July 2004
    Posts
    1,330

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

    one formula based approach

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th March 2003
    Posts
    66

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 7
    Last Post: March 30th, 2008, 07:39
  2. Replies: 4
    Last Post: October 7th, 2006, 04:04
  3. Replies: 1
    Last Post: October 18th, 2005, 17:45
  4. Replies: 1
    Last Post: July 21st, 2004, 00:23
  5. Remove last character if it is a comma
    By jefsboys in forum EXCEL HELP
    Replies: 4
    Last Post: July 9th, 2004, 00:51

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