Splitting a cell string depending on variables

**Dave_09** · 03-01-2009, 09:06 AM

Hi all, new to the forums but from the brief look i've had looks like i'll be here quite a lot :-)

On to what brought me here; I have a spreadsheet with a column containing an address, eg '12 SMITH ST SOMESUBURB NSW 2001'. I'm looking at splitting this cell in half after the street and moving the second half (suburb state postcode) into the next cell, which i've managed to do with a series on InStr statements. Now the fun part is that not all these addresses are streets, some are avenues (AV or AVE), roads (RD), place (PL), drive (DR or DRV) etc. Initially i just did this:

Please Login or Register  to view this content.

... with the 'If' statement being expanded for 'AV' and 'RD' etc as needed. But of course when you have 50+ possible street types to scan for this isn't the most efficient. I'm thinking this is a good place for a loop that reads in each of these possible street types and once found splits and moves it to the next cell before moving on. Just how i do this is where i could do with some help. Other things i have to deal with is that not all the street typed are 2 digits, 'AVE' is 3, 'COVE' is 4...

Any help would be much appreciated :-)

**ravishankar** · 03-01-2009, 09:56 AM

Hi
List the 50 possible matches in col Z and insert these codes at appropriate place.

Please Login or Register  to view this content.

Ravi

**Leith Ross** · 03-01-2009, 01:43 PM

Hello Dave_09,

There is a tool designed just for these types of comparisons. Not many people in VBA know about. It is called Regular Expressions. If you post a list of what you need, I can write the code comparison routine using regular expressions for you. It is the quickest method available for this.

**shg** · 03-01-2009, 02:21 PM

Here's a function you could use:

Please Login or Register  to view this content.

It returns an array; so if there is an address in A1, select B1 and C1, paste this in the formula bar,

=SplitAddress(A1)

... press and hold the Ctrl and Shift keys, then press Enter.

You can change sList to contain more possible aliases for "Street"

**Dave_09** · 03-02-2009, 05:32 AM

Hey thanks all for the quick responses!

Ok, currently i have an address column (J) containing address stings such as:

Please Login or Register  to view this content.

The macro i originally made was just to sort it from a paste into another worksheet. I'd paste the data into worksheet one, run the macro and then worksheet 2 would have all the data i needed in a neat order.

The SplitAddress function works fantastic, thanks! I highlighted the corresponding rows in some spare comlums (U and V) and referenced the corresponding J cell and split them nicely. Is there a way i can automate this as well, so i can just Run the macro rather then having to paste the formula in on the spreadsheet? I tried

Please Login or Register  to view this content.

But this just does the first part of the split in rows U and V, which i guess is right. I need to tell it in the second command to do the end part. And also, i want it to skip doing anything when it see's a 'PO BOX...'. At the moment it puts the full address in the first part of the split, i'd prefer it to just leave it blank. Guess that just needs an If statement?

Thanks again, been a great help

**shg** · 03-02-2009, 10:51 AM

Please Login or Register  to view this content.

**Dave_09** · 03-02-2009, 07:24 PM

That did it

Cheers!

**shg** · 03-02-2009, 07:32 PM

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes