Hey everyone,

Excel 2003 or 2007 - I have both

I have a column of addresses that were entered into one cell at a time using
ALT + Enter for the individual address lines. For example, ONE cell will
look like this:

John Freeman
123 Melody Lane
Nutley, NJ 07110

I need to separate everything into multiple columns. So ONE column will
just contain the first & last names, another will contain the Street Address,
another contains the City, so on a so forth.

I know about the CLEAN function to remove the hard returns. The only
problem is separating the data. I was thinking if I could insert a semicolon
where all of the Hard Returns are for all the address, I could then use
Text-to-Columns to separate it out the way I want to.

Is there a way to do this? THANKS!
Joe

You should be able to do it without using that intermediate step.

Select your range
Data|Text to columns (xl2003 menus)
Delimited
By:
ctrl-j
It may look like nothing is in that box, but try it.

And finish up.

You can actually use the same ctrl-j in the Edit|replace dialog, too.

Access Joe wrote:

Hey everyone,

Excel 2003 or 2007 - I have both

I have a column of addresses that were entered into one cell at a time using
ALT + Enter for the individual address lines. For example, ONE cell will
look like this:

John Freeman
123 Melody Lane
Nutley, NJ 07110

I need to separate everything into multiple columns. So ONE column will
just contain the first & last names, another will contain the Street Address,
another contains the City, so on a so forth.

I know about the CLEAN function to remove the hard returns. The only
problem is separating the data. I was thinking if I could insert a semicolon
where all of the Hard Returns are for all the address, I could then use
Text-to-Columns to separate it out the way I want to.

Is there a way to do this? THANKS!
Joe

--

Dave Peterson

You can use Text To Columns directly with your existing data... use
Delimited and in the Other field type Ctrl+J... the table will show your
data split apart, simply hit the Finish button to lock it in.

--
Rick (MVP - Excel)


"Access Joe" wrote in message
...
Hey everyone,

Excel 2003 or 2007 - I have both

I have a column of addresses that were entered into one cell at a time
using
ALT + Enter for the individual address lines. For example, ONE cell will
look like this:

John Freeman
123 Melody Lane
Nutley, NJ 07110

I need to separate everything into multiple columns. So ONE column will
just contain the first & last names, another will contain the Street
Address,
another contains the City, so on a so forth.

I know about the CLEAN function to remove the hard returns. The only
problem is separating the data. I was thinking if I could insert a
semicolon
where all of the Hard Returns are for all the address, I could then use
Text-to-Columns to separate it out the way I want to.

Is there a way to do this? THANKS!
Joe

You can use Find & Replace (CTRL-H) to replace CTRL-J (equivalent to
the hard returns) with a semicolon (or with some other character like
| ). Highlight the column first.

Hope this helps.

Pete

On Feb 22, 6:46*pm, Access Joe
wrote:
Hey everyone,

Excel 2003 or 2007 - I have both

I have a column of addresses that were entered into one cell at a time using
ALT + Enter for the individual address lines. *For example, ONE cell will
look like this:

John Freeman
123 Melody Lane
Nutley, NJ 07110

I need to separate everything into multiple columns. *So ONE column will
just contain the first & last names, another will contain the Street Address,
another contains the City, so on a so forth.

I know about the CLEAN function to remove the hard returns. *The only
problem is separating the data. *I was thinking if I could insert a semicolon
where all of the Hard Returns are for all the address, I could then use
Text-to-Columns to separate it out the way I want to.

Is there a way to do this? *THANKS!
Joe

WOW - that is AWESOME guys! THANK YOU!

"Pete_UK" wrote:

You can use Find & Replace (CTRL-H) to replace CTRL-J (equivalent to
the hard returns) with a semicolon (or with some other character like
| ). Highlight the column first.

Hope this helps.

Pete

On Feb 22, 6:46 pm, Access Joe
wrote:
Hey everyone,

Excel 2003 or 2007 - I have both

I have a column of addresses that were entered into one cell at a time using
ALT + Enter for the individual address lines. For example, ONE cell will
look like this:

John Freeman
123 Melody Lane
Nutley, NJ 07110

I need to separate everything into multiple columns. So ONE column will
just contain the first & last names, another will contain the Street Address,
another contains the City, so on a so forth.

I know about the CLEAN function to remove the hard returns. The only
problem is separating the data. I was thinking if I could insert a semicolon
where all of the Hard Returns are for all the address, I could then use
Text-to-Columns to separate it out the way I want to.

Is there a way to do this? THANKS!
Joe

.