Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cleaning up a huge list (2003 SP2)

    Good afternoon

    I have a huge list of country information that I am trying to tidy up and it is becoming very boring, for instance I have thousands of entries for Canada in a cell followed by other information that I do not require so I thought perhaps I could use find and replace and enter something like Canada*.* and replace with just Canada and then do the same with the USA (I am to frightened to look at that list just yet) etc. Can anybody advise how to word the find and replace

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    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: Cleaning up a huge list (2003 SP2)

    1) you should always do something like this on a copy incase the replace does not do what you expect
    2)canada*.* could work as would canada* from your description. canada*.* will only replace anything with canada and a later period. If there is no period then it will not be replaced

    Another option, if you don't want or need the "global approach" (ie if you need more selectivity) could be to filter the list using autofilter so you can work on subsets in case but this will depend on what you want/need

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up a huge list (2003 SP2)

    Steve

    Without seeing the details of your list it's difficult to know how many countries are involved, but this web page might provide useful information as the basis for a lookup :

    http://www.iso.org/iso/english_country_nam...d_code_elements

    Nick

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up a huge list (2003 SP2)

    Thanks Steve

    Worked great, I realise what you mean about using it with caution, deleting everything after UK* played havoc among my Ukraine entries but fortunately there where only a few. Strangely though Guinea* would not replace anything in any entry for Guinea, even when I thought my old eyes had given up and I copied it from the worksheet and pasted it into the find and replace it still would not have any of it, as there were only 5 listings it did not matter much.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up a huge list (2003 SP2)

    Thanks Nick

    Thats the List I put into an Excel spreadsheet yesterday <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cleaning up a huge list (2003 SP2)

    Have you tried it with 21 shillings? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  7. #7
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up a huge list (2003 SP2)

    OUt of fashion, Hans. He won't remember. Same as groat, ducat, guilder and possibly schilling.
    Sic transit...

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cleaning up a huge list (2003 SP2)

    Thank you young man

    But as a child of threeppenny joeys, silver sixpences and a vague memory of farthings you have greatly flattered me :-)

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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