Find and Replace Excell 2003

Big X · 08-06-2008, 05:21 PM

Got a bit of a problem with some data I received in excel. Thought I would ask you to see if you know the answer. Basically I have an address column with all the values in one column. I basically need to separate them into separate columns so I can use them in a mail merge in correct format. This is exactly how the data is written within one column.

Abb Australia Pty Limited / 601-609 Blackburn Road / NOTTING HILL VIC 3168

Adaps / 390 St Kilda Road / MELBOURNE VIC 3004

Adaps / 390 St Kilda Road / MELBOURNE VIC 3004

Adaps / 390 St Kilda Road / MELBOURNE VIC 3004

Adaps / 390 St Kilda Road / MELBOURNE VIC 3004

Adapt Essential Services Pty Ltd / 2/28 Superior Drive / DANDENONG SOUTH VIC 3175

Adolescent Recovery Centre / 43 Oswald Street / DANDENONG VIC 3175

Adolescent Recovery Centre / 43 Oswald Street / DANDENONG VIC 3175

So basically so far I have created a new column cut and past and in find and replace I entered

Find */ and replace with black and that gave me

NOTTING HILL VIC 3168

So that will work to get the second address line, I then did the opposite

Find /* replace with nothing.

Abb Australia Pty Limited

And that gave me the Company name by itself. The problem is step 3. I can’t work out how to get the 1st address line with find and replace by it’s self.

i.e.

601-609 Blackburn Road

390 St Kilda Road

If you know the trick let me know its, about 950 records so I kinda got to use a auto function like find and replace. I’ve been looking on the net and cant find an example. If not will have to tell customer know I need them to send me better data! J.

Big X · 08-06-2008, 06:57 PM

Well i found a solution and ill post it here just incase some one else comes up against it.

Highlight the whole column and select Edit -> Replace.

In the Find what field, type “ / “ (i.e. space, forward slash, space) (don’t type the quotation marks though).

In the Replace with field type ; (ie. semi-colon with no spaces).

Click Replace All.

You have to do this so the slashes that belong in some of the addresses (eg. 2/28 Superior Drive) don’t get confused with the slashes that signify a split line.

Once all the slashes have been replaced with a semi-colon, highlight the whole column and click Data -> Text to Columns. Then click Delimited and Next. Deselect Tab and select Semicolon. Click Next and then click Finish.

08-06-2008, 05:21 PM	#1 (permalink)
Big X Registered User Join Date: Jun 2006 Posts: 39 OS: XP Home SP3, Vista Ultimate 64 Bit SP1, Vista 32 Bit Ultimate	Find and Replace Excell 2003 Got a bit of a problem with some data I received in excel. Thought I would ask you to see if you know the answer. Basically I have an address column with all the values in one column. I basically need to separate them into separate columns so I can use them in a mail merge in correct format. This is exactly how the data is written within one column. Abb Australia Pty Limited / 601-609 Blackburn Road / NOTTING HILL VIC 3168 Adaps / 390 St Kilda Road / MELBOURNE VIC 3004 Adaps / 390 St Kilda Road / MELBOURNE VIC 3004 Adaps / 390 St Kilda Road / MELBOURNE VIC 3004 Adaps / 390 St Kilda Road / MELBOURNE VIC 3004 Adapt Essential Services Pty Ltd / 2/28 Superior Drive / DANDENONG SOUTH VIC 3175 Adolescent Recovery Centre / 43 Oswald Street / DANDENONG VIC 3175 Adolescent Recovery Centre / 43 Oswald Street / DANDENONG VIC 3175 So basically so far I have created a new column cut and past and in find and replace I entered Find / and replace with black and that gave me NOTTING HILL VIC 3168 So that will work to get the second address line, I then did the opposite Find / replace with nothing. Abb Australia Pty Limited And that gave me the Company name by itself. The problem is step 3. I can’t work out how to get the 1st address line with find and replace by it’s self. i.e. 601-609 Blackburn Road 390 St Kilda Road If you know the trick let me know its, about 950 records so I kinda got to use a auto function like find and replace. I’ve been looking on the net and cant find an example. If not will have to tell customer know I need them to send me better data! J.

08-06-2008, 06:57 PM	#2 (permalink)
Big X Registered User Join Date: Jun 2006 Posts: 39 OS: XP Home SP3, Vista Ultimate 64 Bit SP1, Vista 32 Bit Ultimate	Re: Find and Replace Excell 2003 Well i found a solution and ill post it here just incase some one else comes up against it. Highlight the whole column and select Edit -> Replace. In the Find what field, type “ / “ (i.e. space, forward slash, space) (don’t type the quotation marks though). In the Replace with field type ; (ie. semi-colon with no spaces). Click Replace All. You have to do this so the slashes that belong in some of the addresses (eg. 2/28 Superior Drive) don’t get confused with the slashes that signify a split line. Once all the slashes have been replaced with a semi-colon, highlight the whole column and click Data -> Text to Columns. Then click Delimited and Next. Deselect Tab and select Semicolon. Click Next and then click Finish.