Split Suburb, state and postcode into three seperate colums

[bigburgo]

Hi I am looking for some help in how to split the follow addresses into two separate boxes. Example of current database.

Lucas Heights 2234
Sylvania 2224
Barden Ridge 2234

I need Lucas Heights in one box and 2234 in another, any ideas of a macro?

Regards,

Rob.

 

[Tom Urtis]

"Split Suburb, state and postcode into three seperate colums "

Where is the state in your example data?

And why do you want a macro?

If it's only 2 columns, and your list is in column A starting with cell A1, then in cell B1 enter and copy down
=TRIM(SUBSTITUTE(A1,C1,""))

and in C1 enter and copy down
=RIGHT(A1,4)

 

[bigburgo]

HI Tom I did have state as well but as it was the same state for each location just did a replace with nothing and added another column with the state name.

Thanks for that worked like a charm.

Cheers

 

[bigburgo]

The only thing now is if I want to delete the orignal box with both suburb and postcode I am going to lose the next two boxes aswell....

How do I keep the new data and get rid of the original column?

Rob.

 

[Tom Urtis]

After you complete the steps from my first post, select any cell in column A, B, or C that is in the range, such as A8 or B5 or C22. Doesn't matter which cell you select as long as it is in the used range. With a cell selected, press F5 > Special > Current Region and click OK.

Press Ctrl+C. Then, right click anywhere in that range and select Paste Special. In the Paste Special Dialog box, select values. Click OK. Press the Esc key. Right click the column A header, and select Delete.

 

[bigburgo]

Thanks Tom,

all done. Cheers

 

[bigburgo]

Tom,
one more thing,

Do you know how I search for duplicates in the address database I now have.

this is an example address.

Belrose Kindergarten / 3 Ralston Avenue / Belrose / NSW / 2085 / Ph: (02) 9451 2303 / lower Northern Beaches /

I have used / to seperate rows.

I have a database of about 3000 and trying to sort duplicates.

 

[Blade Hunter]

Highlight your data and choose advanced filter. In older versions of Excel it is in the data manu, newer ones have it on the data ribbon bar.

Choose "Copy to another location" then Cell A1 on a new tab

Tick "Unique records only"

Click OK.

Let it do it's thing, 3000 records will take it a little while so maybe give it a few minutes.

Cheers

Dan

 

[bigburgo]

HI Guys,

I have one more list I need to do, I have tried myself but don't really understand this stuff so failed badly.

I need these changed from Vertical to horizontal, there is no gap between the addresses in excell and each entry is 8 rows deep and two collums across.

School name Redfield College
Postal address 855 Old Northern Road
Dural**NSW**2158
Telephone 02 9651 4066
Facsimile 02 9651 3789
Headmaster Mr Andrew Mullins
Enrolments Karen Clowes (Registrar)
Religious affiliation Independent Catholic
School web site www.redfield.nsw.edu.au
School name Rouse Hill Anglican College
Postal address PO Box 3296
Rouse Hill**NSW**2155
Telephone 02 8824 5844
Facsimile 02 8824 5877
Principal Peter Fowler
Enrolments Beth Chant (Registrar)
Religious affiliation Anglican
School web site www.rhac.nsw.edu.au

Cheers

Rob.

 

[bigburgo]

I just realised they are actually 9 deep.

The Address is two rows eg.

School Name / Redfield College
Postal Address / 855 Old Northern Road
(BLANK CELL) / Dural NSW 2158
Telephone / 02 9651 4066

etc.

Cheers

Rob.