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, quot;JoannaFquot;
gt; wrote:
gt;I have cells of block addresses that are delimited (tab I believe - there's a
gt;small square that appears).
In Excel, a lt;tabgt; is displayed as white space, not as a small square.
gt;I need to split the addresses out into separate
gt;columns for each line of address. I tried using the Text to Columns wizard
gt;as suggested by Excel Help, but ended up with only the first line of address
gt;being posted in the cell to the right (the rest of the address was nowhere to
gt;be seen).
No where? Not even in the original cell?gt;I'm sure I've done this before but can't for the life of me
gt;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
quot;Ron Rosenfeldquot; wrote:
gt; On Mon, 20 Feb 2006 09:21:30 -0800, quot;JoannaFquot;
gt; gt; wrote:
gt;
gt; gt;I have cells of block addresses that are delimited (tab I believe - there's a
gt; gt;small square that appears).
gt;
gt; In Excel, a lt;tabgt; is displayed as white space, not as a small square.
gt;
gt; gt;I need to split the addresses out into separate
gt; gt;columns for each line of address. I tried using the Text to Columns wizard
gt; gt;as suggested by Excel Help, but ended up with only the first line of address
gt; gt;being posted in the cell to the right (the rest of the address was nowhere to
gt; gt;be seen).
gt;
gt; No where? Not even in the original cell?
gt;
gt;
gt; gt;I'm sure I've done this before but can't for the life of me
gt; gt;remember how I did it...
gt;
gt; Is the entire address in a single cell?
gt;
gt; Can you post a sample of what it looks like and how you want it split up?
gt;
gt; Given your description, I wonder if it really is tab-delimited, or if that is
gt; some other character.
gt;
gt;
gt; --ron
gt;
On Tue, 21 Feb 2006 01:53:27 -0800, quot;JoannaFquot;
gt; wrote:
gt;Hi Ron,
gt;Thanks for answering.
gt;If I copy and past a sample cell into this discussion page, the formatting
gt;is lost, therefore I've emailed it to the address shown on your profile.
gt;Hope that's ok.
gt;I appreciate your input.
gt;Cheers
gt;Jo
gt;
gt;quot;Ron Rosenfeldquot; wrote:
gt;
gt;gt; On Mon, 20 Feb 2006 09:21:30 -0800, quot;JoannaFquot;
gt;gt; gt; wrote:
gt;gt;
gt;gt; gt;I have cells of block addresses that are delimited (tab I believe - there's a
gt;gt; gt;small square that appears).
gt;gt;
gt;gt; In Excel, a lt;tabgt; is displayed as white space, not as a small square.
gt;gt;
gt;gt; gt;I need to split the addresses out into separate
gt;gt; gt;columns for each line of address. I tried using the Text to Columns wizard
gt;gt; gt;as suggested by Excel Help, but ended up with only the first line of address
gt;gt; gt;being posted in the cell to the right (the rest of the address was nowhere to
gt;gt; gt;be seen).
gt;gt;
gt;gt; No where? Not even in the original cell?
gt;gt;
gt;gt;
gt;gt; gt;I'm sure I've done this before but can't for the life of me
gt;gt; gt;remember how I did it...
gt;gt;
gt;gt; Is the entire address in a single cell?
gt;gt;
gt;gt; Can you post a sample of what it looks like and how you want it split up?
gt;gt;
gt;gt; Given your description, I wonder if it really is tab-delimited, or if that is
gt;gt; some other character.
gt;gt;
gt;gt;
gt;gt; --ron
gt;gt;
Perhaps you could paste a shortened version here.
--ron
Here's one sample cell:
quot;St James Court
Great Park Road
Almondsbury Park
Bradley Stoke
Bristolquot;On Tue, 21 Feb 2006 06:44:28 -0800, quot;JoannaFquot;
gt; wrote:
gt;Here's one sample cell:
gt;
gt;quot;St James Court
gt;Great Park Road
gt;Almondsbury Park
gt;Bradley Stoke
gt;Bristolquot;
OK, the issue is that your quot;separatorquot; is actually lt;CR-LFgt; (two characters).
I have two solutions for you:
-------------------------
#1
With your data in A1:An
B1: =SUBSTITUTE(A1,CHAR(13)amp;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
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,quot;.*quot;,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
quot;Ron Rosenfeldquot; wrote:
gt; On Tue, 21 Feb 2006 06:44:28 -0800, quot;JoannaFquot;
gt; gt; wrote:
gt;
gt; gt;Here's one sample cell:
gt; gt;
gt; gt;quot;St James Court
gt; gt;Great Park Road
gt; gt;Almondsbury Park
gt; gt;Bradley Stoke
gt; gt;Bristolquot;
gt;
gt; OK, the issue is that your quot;separatorquot; is actually lt;CR-LFgt; (two characters).
gt;
gt; I have two solutions for you:
gt;
gt; -------------------------
gt; #1
gt;
gt; With your data in A1:An
gt;
gt; B1: =SUBSTITUTE(A1,CHAR(13)amp;CHAR(10),CHAR(9))
gt;
gt; copy/drag down as far as needed.
gt;
gt; Select B1:Bn
gt;
gt; Edit/Copy
gt; Edit/Paste Special/Values
gt;
gt; Data/Text to Columns
gt; Delimited
gt; Tab
gt; Finish
gt;
gt; Delete column A if desired
gt;
gt; --------------------------------
gt;
gt; #2
gt;
gt; Download and install Longre's free morefunc.xll add-in from
gt; xcell05.free.fr/ (There is an option to distribute the add-in with the
gt; workbook, if that is an issue for you).
gt;
gt; B1:=REGEX.MID($A$1,quot;.*quot;,COLUMNS($A:A))
gt;
gt; Copy/Drag across as far as needed to include all the lines; e.g. to F1 if your
gt; example is representative.
gt;
gt; Select B1:F1 and copy/drag down to Row n
gt;
gt; If it is desired to delete column A, first Copy/Paste Special Values over these
gt; formulas.
gt;
gt; ------------------------------------
gt; --ron
gt;
On Tue, 21 Feb 2006 08:39:12 -0800, quot;JoannaFquot;
gt; wrote:
gt;Brilliant! Thanks Ron. I tried the first suggestion - it worked perfectly.
gt;Thank you very much for your input.
gt;
gt;Cheers
gt;Jo
gt;
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:
quot;Smith, Maria
1234 East Summit
San Antonio TX 12345
Home 734-1234/cell 602-1234
quot;
Also, how do I propagate the formula suggested by Ron
B1: =SUBSTITUTE(A1,CHAR(13)amp;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)
quot;Ron Rosenfeldquot; wrote:
gt; On Tue, 21 Feb 2006 06:44:28 -0800, quot;JoannaFquot;
gt; gt; wrote:
gt;
gt; gt;Here's one sample cell:
gt; gt;
gt; gt;quot;St James Court
gt; gt;Great Park Road
gt; gt;Almondsbury Park
gt; gt;Bradley Stoke
gt; gt;Bristolquot;
gt;
gt; OK, the issue is that your quot;separatorquot; is actually lt;CR-LFgt; (two characters).
gt;
gt; I have two solutions for you:
gt;
gt; -------------------------
gt; #1
gt;
gt; With your data in A1:An
gt;
gt; B1: =SUBSTITUTE(A1,CHAR(13)amp;CHAR(10),CHAR(9))
gt;
gt; copy/drag down as far as needed.
gt;
gt; Select B1:Bn
gt;
gt; Edit/Copy
gt; Edit/Paste Special/Values
gt;
gt; Data/Text to Columns
gt; Delimited
gt; Tab
gt; Finish
gt;
gt; Delete column A if desired
gt;
gt; --------------------------------
gt;
gt; #2
gt;
gt; Download and install Longre's free morefunc.xll add-in from
gt; xcell05.free.fr/ (There is an option to distribute the add-in with the
gt; workbook, if that is an issue for you).
gt;
gt; B1:=REGEX.MID($A$1,quot;.*quot;,COLUMNS($A:A))
gt;
gt; Copy/Drag across as far as needed to include all the lines; e.g. to F1 if your
gt; example is representative.
gt;
gt; Select B1:F1 and copy/drag down to Row n
gt;
gt; If it is desired to delete column A, first Copy/Paste Special Values over these
gt; formulas.
gt;
gt; ------------------------------------
gt; --ron
gt;
On Fri, 28 Apr 2006 00:39:01 -0700, jp.martinez
gt; wrote:
gt;I need to split the data from a single cell into individual cells. I have
gt;name, address, phone(s), and email. I tried the solution # 1 posted below by
gt;Ron for a similar situation, but I ended up with only first and last name
gt;being displayed. What am I missing? Or should I be using a different
gt;approach?
gt;
gt;This is a sample of the data I am using:
gt;
gt;quot;Smith, Maria
gt;1234 East Summit
gt;San Antonio TX 12345
gt;Home 734-1234/cell 602-1234
quot;
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 quot;Nonequot; where it
asks for a quot;text qualifierquot;.
------------------------------
The REGEX solution will also work, with a slight rewriting of the formula:B1:=REGEX.MID($A1,quot;[^quot;quot;\n]*quot;,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.
gt;
gt;Also, how do I propagate the formula suggested by Ron
gt;
gt;B1: =SUBSTITUTE(A1,CHAR(13)amp;CHAR(10),CHAR(9))
gt;
gt;To all the cells in column B, replacing A1 for A2, A3, and so forth? My
gt;list has 106 entries.
gt;
gt;I would be eternally grateful for your guidance.
gt;
gt;JP
gt;
gt;(See below for the message I am referring to)
gt;
gt;quot;Ron Rosenfeldquot; wrote:
gt;
gt;gt; On Tue, 21 Feb 2006 06:44:28 -0800, quot;JoannaFquot;
gt;gt; gt; wrote:
gt;gt;
gt;gt; gt;Here's one sample cell:
gt;gt; gt;
gt;gt; gt;quot;St James Court
gt;gt; gt;Great Park Road
gt;gt; gt;Almondsbury Park
gt;gt; gt;Bradley Stoke
gt;gt; gt;Bristolquot;
gt;gt;
gt;gt; OK, the issue is that your quot;separatorquot; is actually lt;CR-LFgt; (two characters).
gt;gt;
gt;gt; I have two solutions for you:
gt;gt;
gt;gt; -------------------------
gt;gt; #1
gt;gt;
gt;gt; With your data in A1:An
gt;gt;
gt;gt; B1: =SUBSTITUTE(A1,CHAR(13)amp;CHAR(10),CHAR(9))
gt;gt;
gt;gt; copy/drag down as far as needed.
gt;gt;
gt;gt; Select B1:Bn
gt;gt;
gt;gt; Edit/Copy
gt;gt; Edit/Paste Special/Values
gt;gt;
gt;gt; Data/Text to Columns
gt;gt; Delimited
gt;gt; Tab
gt;gt; Finish
gt;gt;
gt;gt; Delete column A if desired
gt;gt;
gt;gt; --------------------------------
gt;gt;
gt;gt; #2
gt;gt;
gt;gt; Download and install Longre's free morefunc.xll add-in from
gt;gt; xcell05.free.fr/ (There is an option to distribute the add-in with the
gt;gt; workbook, if that is an issue for you).
gt;gt;
gt;gt; B1:=REGEX.MID($A$1,quot;.*quot;,COLUMNS($A:A))
gt;gt;
gt;gt; Copy/Drag across as far as needed to include all the lines; e.g. to F1 if your
gt;gt; example is representative.
gt;gt;
gt;gt; Select B1:F1 and copy/drag down to Row n
gt;gt;
gt;gt; If it is desired to delete column A, first Copy/Paste Special Values over these
gt;gt; formulas.
gt;gt;
gt;gt; ------------------------------------
gt;gt; --ron
gt;gt;
--ron
留言列表
