Reply With QuoteI'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.
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
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.
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.
Sorry, not very helpful, but Access should have been used instead of Excel, or any proper database package.
Look for 'text to columns' in the data tab. If there are commas in the addresses you can split the data by those.
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
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.Originally Posted by RS404
Sent from my iPhone using Tapatalk
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.
Don’t think any solution will work 100%. Not all streets have a recognised suffix for such.
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.
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).
Genius!
You haven't seen the dataset. How do you know each address contains a postcode & if it does what the format is?
All thanks for your suggestions, time and help.
We are in a good enough state now.
Sent from my iPhone using Tapatalk
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