Insert a semicolon in lieu of ALT Enter

  • Thread starter Thread starter Access Joe
  • Start date Start date
A

Access Joe

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 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.
 
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.
 
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
 
WOW - that is AWESOME guys! THANK YOU!

Pete_UK said:
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



.
Click to expand...
 
Back
Top