Is Excel gonna be a convenient tool for solving this problem ?

Phalanx_

New Member
Joined
Apr 15, 2013
Messages
13
Hello everyone,

I would like to start saying that I am very new 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.

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.

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.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Welcome to MrExcel.

You can use text functions like LEFT, MID and FIND. But if I were you I would have (at least) four columns for the address with each part in a separate column. Addresses can be complex to parse and there is no guarantee that the user will separate the parts with a comma as in your example.
 
Upvote 0
Will you be using the same file for everyone? If yes, keep in mind that the other people will be able to see the info entered be others, you can hide it from the day-to-day users, but there is no way of preventing an experienced user from accessing all the collected data.

If this is not a problem, then yes, you can use Excel...


You need to learn some basic VBA code, userforms, TextBox, Combobox (populated by a list, preferable a named list, so names also; also the use of the vlookup formula. this part is for the "all the buildings in the street" part). I think that is it. Sounds like something really simple actually.

There is a book for beginners "Microsoft Excel VBA Programming for Dummies 2010" (I don't know if that is the last version), is a really easy well explained book, and by reading it you should be able to do this in a breeze.


----

What Andrew says is a (simple) solution, but you'd need to trust people not to change other people's cells.
 
Last edited:
Upvote 0
Thank you Andrew and Jeffrey, I appreciate these informations.

I don't really have a problem with info being visible to everyone actually, what I want is to have a tool to break a full address into smaller components in order to simplify the process of creating a database. So just by typing one sentence, Excel would be able to directly fill in all the detailed columns, and later I could add some functionalities according to the content of the columns.

I started to do some research according to what you suggested me and I feel concern with the notion of "pattern" in the input. The fact that there won't be a standard pattern in my input (user could write "140 Commonwealth Ave, Chestnut Hill, MA 02467, United States" as well as "140 Commonwealth Avenue Chestnut Hill MA 02467 US" without comas for example), is it gonna keep me from creating my tool ? It seems hard to conceive a tool which can identify which part of the address actually corresponds to the name of the street for example (and not the name of the state, or whatever else).
 
Upvote 0
Hi and welcome to MrExcel.

On the back of what Andrew has said regarding MID etc, these maybe helpful....

Mr Excel & excelisfun Trick 53: Extract State When Text String Has Varying Number of Commas - YouTube

MrExcel's Learn Excel #417 - Parsing Addresses - YouTube

It's probably best to set up your worksheet so that each part of the address has it's own column rather than having it all in one.

You may want to take a look here as well....

Create an Excel UserForm

If the user has to enter the data into a Userform, you may have better control over what they enter.

You will find plenty of helpful videos and information at the above sites for what you want to do, oh yeah and you'll find plenty of advice and help here! :biggrin:

Good luck with your project.

Ak
 
Upvote 0
Thank you Akashwani for the advices and the link. The two videos about parsing an address are awesome, and made me realize that there might be no way to do what I want to do unless I have a standard way of typing the address as an input (as for Mike and Bill in the video having the state always after the last coma).

I am gonna work on a way to set a pattern so each information can be accessed via a formula by Excel even if not every input has the same organization. Like in their video actually, inputs have different style :
- line 1: 1Street_No 2Street_Name , 3City , 4State 5Num
- line 2: 1Street_No 2Street_Name , 3Building_No , 4Room , 5City , 6State 7Num

I wish they had made a video with a parsing for every element! Because it seems hard to design a function which can select the element 3Building_No from line 2 without selecting anything from line 1 (because the pattern to identify 3Building_No could also be the one for 3City in line 1 as they are both located after the first coma and before the second coma).
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top