how to split address blocks across multiple cells

[JoannaF]

I have cells of block addresses that are delimited (tab I believe - there's a
small square that appears). I need to split the addresses out into separate
columns for each line of address. I tried using the Text to Columns wizard
as suggested by Excel Help, but ended up with only the first line of address
being posted in the cell to the right (the rest of the address was nowhere to
be seen). I'm sure I've done this before but can't for the life of me
remember how I did it...

 

[Ron Rosenfeld]

I have cells of block addresses that are delimited (tab I believe - there's a
small square that appears).

I need to split the addresses out into separate
columns for each line of address. I tried using the Text to Columns wizard
as suggested by Excel Help, but ended up with only the first line of address
being posted in the cell to the right (the rest of the address was nowhere to
be seen).

No where? Not even in the original cell?

I'm sure I've done this before but can't for the life of me
remember how I did it...

Is the entire address in a single cell?

Can you post a sample of what it looks like and how you want it split up?

Given your description, I wonder if it really is tab-delimited, or if that is
some other character.


--ron

 

[JoannaF]

Hi Ron,
Thanks for answering.
If I copy and past a sample cell into this discussion page, the formatting
is lost, therefore I've emailed it to the address shown on your profile.
Hope that's ok.
I appreciate your input.
Cheers
Jo

 

[Ron Rosenfeld]

Hi Ron,
Thanks for answering.
If I copy and past a sample cell into this discussion page, the formatting
is lost, therefore I've emailed it to the address shown on your profile.
Hope that's ok.
I appreciate your input.
Cheers
Jo

Perhaps you could paste a shortened version here.
--ron

 

[JoannaF]

Here's one sample cell:

"St James Court
Great Park Road
Almondsbury Park
Bradley Stoke
Bristol"

 

[Ron Rosenfeld]

Here's one sample cell:

"St James Court
Great Park Road
Almondsbury Park
Bradley Stoke
Bristol"

OK, the issue is that your "separator" is actually <CR-LF> (two characters).

I have two solutions for you:

-------------------------
#1

With your data in A1:An

B1: =SUBSTITUTE(A1,CHAR(13)&CHAR(10),CHAR(9))

copy/drag down as far as needed.

Select B1:Bn

Edit/Copy
Edit/Paste Special/Values

Data/Text to Columns
Delimited
Tab
Finish

Delete column A if desired

--------------------------------

#2

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ (There is an option to distribute the add-in with the
workbook, if that is an issue for you).

B1: =REGEX.MID($A$1,".*",COLUMNS($A:A))

Copy/Drag across as far as needed to include all the lines; e.g. to F1 if your
example is representative.

Select B1:F1 and copy/drag down to Row n

If it is desired to delete column A, first Copy/Paste Special Values over these
formulas.

 

[JoannaF]

Brilliant! Thanks Ron. I tried the first suggestion - it worked perfectly.
Thank you very much for your input.

Cheers
Jo

 

[Ron Rosenfeld]

Brilliant! Thanks Ron. I tried the first suggestion - it worked perfectly.
Thank you very much for your input.

Cheers
Jo

Glad to have helped. Thanks for the feedback.
--ron

 

[jp.martinez]

I need to split the data from a single cell into individual cells. I have
name, address, phone(s), and email. I tried the solution # 1 posted below by
Ron for a similar situation, but I ended up with only first and last name
being displayed. What am I missing? Or should I be using a different
approach?

This is a sample of the data I am using:

"Smith, Maria
1234 East Summit
San Antonio TX 12345
Home 734-1234/cell 602-1234
(e-mail address removed)"

Also, how do I propagate the formula suggested by Ron

B1: =SUBSTITUTE(A1,CHAR(13)&CHAR(10),CHAR(9))

To all the cells in column B, replacing A1 for A2, A3, and so forth? My
list has 106 entries.

I would be eternally grateful for your guidance.

JP

(See below for the message I am referring to)

 

[Ron Rosenfeld]

I need to split the data from a single cell into individual cells. I have
name, address, phone(s), and email. I tried the solution # 1 posted below by
Ron for a similar situation, but I ended up with only first and last name
being displayed. What am I missing? Or should I be using a different
approach?

This is a sample of the data I am using:

"Smith, Maria
1234 East Summit
San Antonio TX 12345
Home 734-1234/cell 602-1234
(e-mail address removed)"

There are differences between the data you posted here and JoannaF's data.
Your data does NOT have lines separated by CR-LF but rather just by a LF. So
if you want to use the SUBSTITUTE function followed by the Copy/Paste Special
Values and Data/Text-to-columns routine, you will need to change that formula
to:

B1: =SUBSTITUTE(A1,CHAR(10),CHAR(9))

Also, in the Data/Text-to-columns wizard, be sure to select "None" where it
asks for a "text qualifier".

------------------------------
The REGEX solution will also work, with a slight rewriting of the formula:


B1: =REGEX.MID($A1,"[^""\n]*",COLUMNS($B:B))

------------------------------------

Either formula, written as above, will adjust its references as you drag it
down and/or, in the case of the REGEX solution, down and across.

Also, how do I propagate the formula suggested by Ron

B1: =SUBSTITUTE(A1,CHAR(13)&CHAR(10),CHAR(9))

To all the cells in column B, replacing A1 for A2, A3, and so forth? My
list has 106 entries.

I would be eternally grateful for your guidance.

JP

(See below for the message I am referring to)

--ron

 

[jp.martinez]

Ron, Thanks for your reply.

I went throught the steps, but the end result only displays last and first
names. The rest of the data is lost.

On the Data/ Text-to-columns Wizard preview (step 1) it shows all the data,
but when I click NEXT to go to step two, the screen already shows last, first
name only. I tried using TAB as delimiter, and I also tried each of the
other delimiter options (inluding not selecting any) but none work. In every
case I used "none" as the text qualifier.

Step by Step, this is what I did, I include data from a sample cell: (My
data starts on cell A2)

"Smith, Maria
1234 East Summit
San Antonio TX 54321
Home 734-1234/cell 602-4321
(e-mail address removed)"

B2: =SUBSTITUTE(A2,CHAR(10),CHAR(9))

copy/drag the formula from B2 to B106

Selected B2:B106

Edit/Copy

Edit/Paste Special/Values

Data/Text to Columns

Delimited

Tab

Finish

My end result is the list of first and last names. All other data is lost.

Any thoughts? Sorry about the trouble

 

[Ron Rosenfeld]

Ron, Thanks for your reply.

I went throught the steps, but the end result only displays last and first
names. The rest of the data is lost.

On the Data/ Text-to-columns Wizard preview (step 1) it shows all the data,
but when I click NEXT to go to step two, the screen already shows last, first
name only. I tried using TAB as delimiter, and I also tried each of the
other delimiter options (inluding not selecting any) but none work. In every
case I used "none" as the text qualifier.

Step by Step, this is what I did, I include data from a sample cell: (My
data starts on cell A2)

"Smith, Maria
1234 East Summit
San Antonio TX 54321
Home 734-1234/cell 602-4321
(e-mail address removed)"

B2: =SUBSTITUTE(A2,CHAR(10),CHAR(9))

copy/drag the formula from B2 to B106

Selected B2:B106

Edit/Copy

Edit/Paste Special/Values

Data/Text to Columns

Delimited

Tab

Finish

My end result is the list of first and last names. All other data is lost.

Any thoughts? Sorry about the trouble

On the page where you selected "Tab" as the delimiter, what does it say in the
Text Qualifier box? It should say {none}


--ron

 

[jp.martinez]

Ron,

It does say {none}

The weird thing is that on step one of the Data/ Text to Columns it shows me
all the information for each cell, but as soon as I go to step 2, everything
except first and last name disappears. I selected Tab, and {none} as
qualifier, but the end result is only the names on my list...I don't know
what step I'm missing.

Thanks.

JP

 

[Ron Rosenfeld]

Ron,

It does say {none}

The weird thing is that on step one of the Data/ Text to Columns it shows me
all the information for each cell, but as soon as I go to step 2, everything
except first and last name disappears. I selected Tab, and {none} as
qualifier, but the end result is only the names on my list...I don't know
what step I'm missing.

Thanks.

JP

To me that says there's something about your data or possibly your worksheet
that's not apparent in what has been transmitted here.

I don't know what could be causing things to appear with all the data in Step 1
of the wizard, yet disappear in Step 2

I would take a look at your worksheet, but I'll be out of town until May 14 or
15 and I don't know if the machine to which I'll have access has Excel or not.
But you can try emailing it to me at (reverse the following string, then make
the obvious substitutions) mocTODenilnodlefnesorTApmet

(Paranoia 'R us)


--ron