closing tag is in template navbar
timefactors watches



TZ-UK Fundraiser
Results 1 to 16 of 16

Thread: Now I have an Excel question

  1. #1

    Now I have an Excel question

    Dear Excel Gurus,

    From a spreadsheet of thousands of rows of address information,

    I need to display a column with just the text street names,

    My struggle is selecting the text street names as the data is unstructured. The text street names appear in different columns, sometimes with the numeric building numbers, sometimes not, sometimes with punctuation symbols, sometimes not.

    Any tips? Iíve gone down MID function and post code (again fairly unstructured) look up routes but havenít got far.

    Thanks and regards.


    Sent from my iPad using Tapatalk

  2. #2
    I've also sent a pm but this should be possible with a little thought. Any chance you could email a sample of some data to process? It's hard to visualise without that.

  3. #3
    How about searching for the position of " Rd", " Road", "Cl ", " Ave" etc.?
    Then return everything between the first comma (or number) before that and the end of your search phrase.

    It sounds like it's going to be messy.

  4. #4
    Master
    Join Date
    Dec 2015
    Location
    Between here, there and nowhere
    Posts
    3,245
    Sorry, not very helpful, but Access should have been used instead of Excel, or any proper database package.

  5. #5
    Journeyman RS404's Avatar
    Join Date
    Nov 2016
    Location
    Norwich, Norfolk
    Posts
    191
    Look for 'text to columns' in the data tab. If there are commas in the addresses you can split the data by those.

  6. #6
    Catch21 is kindly going to try and help out with a bit of VBA along the lines of identifying the suffix like Road or Street and then extracting to the left.

    Not my DB, family, I wouldnít tolerate the awful data standards and stewardship!


    Sent from my iPhone using Tapatalk

  7. #7

    Now I have an Excel question

    Quote Originally Posted by RS404 View Post
    Look for 'text to columns' in the data tab. If there are commas in the addresses you can split the data by those.
    This was my first thought when originally explained to me as being a few hundred rows and then offering to help, a bit of text to columns, concatenation.....however itís massive and random! Having said Iíll try to help, I must give it a go.


    Sent from my iPhone using Tapatalk

  8. #8
    Master petethegeek's Avatar
    Join Date
    Jul 2011
    Location
    Worcestershire
    Posts
    1,713
    This is the kind of task which Unix/Linux, along with its inbuilt utilities, can be very good at - excels in you might even say. However I'm guessing you are probably operating in a pure Microsoft environment. Nonetheless many/most Unix tools are now available on windows so exporting to a text format file, selecting rows with a suitable regex and reformatting with awk before re-importing may be a feasible approach.

  9. #9
    Donít think any solution will work 100%. Not all streets have a recognised suffix for such.

  10. #10
    Grand Master hogthrob's Avatar
    Join Date
    Feb 2007
    Location
    Essex, UK
    Posts
    11,667
    For a one off, never to be repeated task, exporting to a text file and then manually inserting a unique separator between addresses may be quicker than trying to automate with VBA or other methods.

  11. #11
    Master amnesia's Avatar
    Join Date
    Mar 2011
    Location
    Peterborough
    Posts
    2,397
    Blog Entries
    1
    Locate the postcode with a regular expression and then automate a call to an address lookup service to get the street name.

    (^o^)

    Sent from my OnePlus 6T using Tapatalk

  12. #12
    Quote Originally Posted by amnesia View Post
    Locate the postcode with a regular expression and then automate a call to an address lookup service to get the street name.

    (^o^)

    Sent from my OnePlus 6T using Tapatalk
    Clever, I like it!!

    Surprisingly (you probably know this but most might not) you can get excel to sequentially call web pages then parse the response for what you want. I do it every week to access everyone's park run results (fellow staff members).

  13. #13
    Craftsman
    Join Date
    Oct 2016
    Location
    UK
    Posts
    296
    Quote Originally Posted by amnesia View Post
    Locate the postcode with a regular expression and then automate a call to an address lookup service to get the street name.

    (^o^)

    Sent from my OnePlus 6T using Tapatalk
    Genius!

  14. #14
    Craftsman
    Join Date
    Jan 2011
    Location
    Maidenhead-ish UK
    Posts
    939
    Quote Originally Posted by amnesia View Post
    Locate the postcode with a regular expression and then automate a call to an address lookup service to get the street name.
    You haven't seen the dataset. How do you know each address contains a postcode & if it does what the format is?

  15. #15
    All thanks for your suggestions, time and help.

    We are in a good enough state now.


    Sent from my iPhone using Tapatalk

  16. #16
    Master amnesia's Avatar
    Join Date
    Mar 2011
    Location
    Peterborough
    Posts
    2,397
    Blog Entries
    1
    Quote Originally Posted by Mr Pointy View Post
    You haven't seen the dataset. How do you know each address contains a postcode & if it does what the format is?
    I don't, but I've done enough data migrations between legacy systems in the last 25 years that I know that if I was trying to sort the problem I'd start with something that has a standard - postcodes / zipcodes can be represented by regular expressions, and therefore conform to a standard.

    Free-text address lines can, and do, contain utter gibberish.

    On top of that, using text expressions in Excel is dull and I'd want a repeatable solution.

Posting Permissions

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