Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find and replace (Excel 2000)

    I am cleaning up some data in a table that I'm going to import to Access. One of the fields in the Excel table is CityStateZip with all three of these items in one cell. I've removed the state (, CA) as they were all the same so now I'm left with the city name and the zip code separated by a space. There are approximately 400 records in this table that I need to remove the zip code from this field. All the zips begin with a 9. Is there any way to do a find for the 9 and four wildcard characters to follow and a replace with null?

  2. #2
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace (Excel 2000)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> I guess I should have looked up "wildcard characters" in the help menu before asking this question!

    Nevermind!

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace (Excel 2000)

    Rather than use Find & Replace, I've set up these formulas to remove extra information:

    A B C

    Taft, CA 93268 =FIND(",",A2) =LEFT(A2,B2-1)

    (you can condense the FIND into the LEFT formula, if you want.)

    Then drag or Copy/Paste this in a free column. Your final cell has the city only, without any trailing comma (thanks to the -1 in col. C). Finally, Copy/Paste Value to fix the city.

    Errol

  4. #4
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace (Excel 2000)

    Thanks so much!

  5. #5
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find and replace (Excel 2000)

    This excel table has a column for phone numbers. Some of the phone numbers are entered (xxx)xxx-xxxx and others are entered xxx.xxx.xxxx.
    I can't figure out how to make them all like the first example above. I want to clean this up before I import it to Access.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find and replace (Excel 2000)

    If those are the only 2 choices try this:
    <pre>=IF(LEFT(A1,1)="(",A1,"("&LEFT(A1,3)&")"&MID( A1,5,3)&"-"&RIGHT(A1,4))</pre>


    Steve

Posting Permissions

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