String of characters recognition without a pattern ?

**Phalanx_** · 04-15-2013, 02:52 AM

Hello everyone,

I would like to start saying that I am a beginner in Excel : of course I have been using it a lot for classical stuff, using some basic functions, but that's it. It seems to me after having read a few topics here that Excel is a powerful tool with functions I would never thought I could find in it.

As I am about to start a new project, I would first ask if Excel is gonna be powerful enough to do this (if not, I should consider finding another software for this matter).
Here is my idea : I would like to have in my excel file an input column called "address" where a user would write a full address. In another part, I would like to have a few more columns with more detailed information (street, house number, city, zip code, etc.), which would be my outputs.
As the user would fill in the input by typing an address, I would like to create a method which would identify some terms of this input and fill up the output accordingly.
The problem is that there might not be a standardize way to write the input, meaning there won't be a pattern enabling to distinguish "street name" from "street number", "state", etc. This is because the user could as well right the address putting comas, just spaces, or writing the name of the zip code before the state or after, and so on.

Example : you type "140 Commonwealth Ave, Chestnut Hill, MA 02467, United States", it would automatically put "140" in the street number, "Commonwealth Avenue" in the Street Name, "Massachusetts" in the state, etc. Same result for this input : "140 Commonwealth Avenue Chestnut Hill MA 02467 US".

Eventually, I am considering to add a database communication to offer some suggestions in output columns if some informations are missing in the full address. Let's say that the user forget to mention the name of the building in the full address but there is a name for the street : the database could provide some content that Excel would help to display (with a spinner containing all the buildings in the street for example).

So here is my two simple questions :
1/ Is Excel a relevant choice for this project ?
2/ If yes, could you have the kindness of mentioning some "methods" or "tools" that Excel provides and that I should study in order to get further ? I really don't know a lot about Excel and some suggestions might help me to focus on the important matter for my project!

Thanks a lot for your time.

**aydeegee** · 04-15-2013, 06:53 AM

Hi Phalanx

It seems to me that you are trying to make a simple operation either difficult or impossible.

In my simple world I would get them to enter data into pre-defined boxes. It might then be possible to data control some areas (eg State). I think that people are used to form filling, so it should not be too diffficult for them. It will also give you opportunity to ensure that there is data in each mandatory field.

You do not say whether all the users would be from one country or worldwide; by entering into separate boxes you would be able to cater for all - including where the zip code (or post code) is shown.

One software provider that provides for worldwide addresses uses:

Address1
Address2
Address3
Address4
Town
State
Post code
Country

(they claim that this covers all eventualities worldwide). I think that I would also include boxes for building number and name.

Hope this helps

Alastair

**Phalanx_** · 04-17-2013, 03:12 AM

Hi aydeegee, thank you for your answer.
I made some researches on my own and I finally reached the same conclusion : analyzing one cell and parsing a complex address which would differ from one line to another (sometimes there is a building name, with a floor, a unit number, sometimes it's just a house, etc.) is impossible.
I made something as you suggested : every time the user clicks on the cell where he used to type the address, a userform pops up instead and allow the user to parse the address himself, and then all the columns are filled immediately !