how to split address blocks across multiple cells

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...

On Mon, 20 Feb 2006 09:21:30 -0800, "JoannaF"
<[email protected]> wrote:

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

In Excel, a <tab> is displayed as white space, not as a small square.

>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

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" wrote:

> On Mon, 20 Feb 2006 09:21:30 -0800, "JoannaF"
> <[email protected]> wrote:
>
> >I have cells of block addresses that are delimited (tab I believe - there's a
> >small square that appears).
>
> In Excel, a <tab> is displayed as white space, not as a small square.
>
> >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
>

On Tue, 21 Feb 2006 01:53:27 -0800, "JoannaF"
<[email protected]> wrote:

>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" wrote:
>
>> On Mon, 20 Feb 2006 09:21:30 -0800, "JoannaF"
>> <[email protected]> wrote:
>>
>> >I have cells of block addresses that are delimited (tab I believe - there's a
>> >small square that appears).
>>
>> In Excel, a <tab> is displayed as white space, not as a small square.
>>
>> >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
>>

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

Here's one sample cell:

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

On Tue, 21 Feb 2006 06:44:28 -0800, "JoannaF"
<[email protected]> wrote:

>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.

------------------------------------
--ron

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

Cheers
Jo

"Ron Rosenfeld" wrote:

> On Tue, 21 Feb 2006 06:44:28 -0800, "JoannaF"
> <[email protected]> wrote:
>
> >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.
>
> ------------------------------------
> --ron
>

On Tue, 21 Feb 2006 08:39:12 -0800, "JoannaF"
<[email protected]> wrote:

>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

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
[email protected]"

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" wrote:

> On Tue, 21 Feb 2006 06:44:28 -0800, "JoannaF"
> <[email protected]> wrote:
>
> >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.
>
> ------------------------------------
> --ron
>

On Fri, 28 Apr 2006 00:39:01 -0700, jp.martinez
<[email protected]> wrote:

>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
>[email protected]"

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 Rosenfeld" wrote:
>
>> On Tue, 21 Feb 2006 06:44:28 -0800, "JoannaF"
>> <[email protected]> wrote:
>>
>> >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.
>>
>> ------------------------------------
>> --ron
>>

--ron

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
[email protected]"

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 Sat, 29 Apr 2006 12:38:01 -0700, jp.martinez
<[email protected]> wrote:

>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
>[email protected]"
>
>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

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


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

On Mon, 1 May 2006 20:07:01 -0700, jp.martinez
<[email protected]> wrote:

>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
>
>
>> On the page where you selected "Tab" as the delimiter, what does it say in the
>> Text Qualifier box? It should say {none}
>>
>>
>> --ron
>>

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