software

I have a column of phone numbers that look like this:
quot;Bureau: (212) 320-3750Direct: (212) 320-3624Outlet: (212) 320-3750

When I copy it to a Word Document, it looks like this:
quot;Bureau: (212) 586-2000
Direct: (212) 830-2502
Outlet: (212) 586-2000
quot;
How can I put these in separate columns?
Angela wrote:
gt; I have a column of phone numbers that look like this:
gt; quot;Bureau: (212) 320-3750Direct: (212) 320-3624Outlet: (212) 320-3750
gt;
gt; When I copy it to a Word Document, it looks like this:
gt; quot;Bureau: (212) 586-2000
gt; Direct: (212) 830-2502
gt; Outlet: (212) 586-2000
gt; quot;
gt; How can I put these in separate columns?

I assume that your column of phone numbers actually looks like this:
Bureau: (212) 586-2000
Direct: (212) 830-2502
Outlet: (212) 586-2000

and that you wish to split the words and the numbers into 2 separate
columns:

=LEFT(A1,FIND(quot; quot;,A1)-1)
=MID(A1,FIND(quot; quot;,A1) 1,LEN(A1))
Well - In the column it looks like this quot;Bureau: (212) 320-3750Direct: (212)
320-3624Outlet: (212) 320-3750 and when I tried that formula it just deleted
evverything except the first word 'Bureauquot;

quot;Paul Lautmanquot; wrote:

gt; Angela wrote:
gt; gt; I have a column of phone numbers that look like this:
gt; gt; quot;Bureau: (212) 320-3750Direct: (212) 320-3624Outlet: (212) 320-3750
gt; gt;
gt; gt; When I copy it to a Word Document, it looks like this:
gt; gt; quot;Bureau: (212) 586-2000
gt; gt; Direct: (212) 830-2502
gt; gt; Outlet: (212) 586-2000
gt; gt; quot;
gt; gt; How can I put these in separate columns?
gt;
gt; I assume that your column of phone numbers actually looks like this:
gt; Bureau: (212) 586-2000
gt; Direct: (212) 830-2502
gt; Outlet: (212) 586-2000
gt;
gt; and that you wish to split the words and the numbers into 2 separate
gt; columns:
gt;
gt; =LEFT(A1,FIND(quot; quot;,A1)-1)
gt; =MID(A1,FIND(quot; quot;,A1) 1,LEN(A1))
gt;
gt;
gt;

Angela,

Try quot;Data:Text to Columnsquot;

HTHquot;Angelaquot; wrote:

gt; I have a column of phone numbers that look like this:
gt; quot;Bureau: (212) 320-3750Direct: (212) 320-3624Outlet: (212) 320-3750
gt;
gt; When I copy it to a Word Document, it looks like this:
gt; quot;Bureau: (212) 586-2000
gt; Direct: (212) 830-2502
gt; Outlet: (212) 586-2000
gt; quot;
gt; How can I put these in separate columns?
gt;
gt;

On Mon, 13 Mar 2006 13:46:27 -0800, Angela gt;
wrote:

gt;I have a column of phone numbers that look like this:
gt;quot;Bureau: (212) 320-3750Direct: (212) 320-3624Outlet: (212) 320-3750
gt;
gt;When I copy it to a Word Document, it looks like this:
gt;quot;Bureau: (212) 586-2000
gt;Direct: (212) 830-2502
gt;Outlet: (212) 586-2000
gt;quot;
gt;How can I put these in separate columns?
gt;

Knowledge of all the possible patterns is important to recommend a functional
formula. For example, if it is always divided into:

Bureau
Direct
Outlet

Then:

=LEFT(A1,FIND(quot;Directquot;,A1)-1)
=MID(A1,FIND(quot;Directquot;,A1),13 9)
=MID(A1,FIND(quot;Outletquot;,A1),255)

will give yo the three types.

If the names of the phone numbers could be anything, one way to extract them
would be with regular expressions.

Download and install Longre's free morefunc.xll add-in from
xcell05.free.fr

Then use the formula:

=REGEX.MID(REGEX.SUBSTITUTE($A$1,quot;\nquot;),quot;[[:alpha:]] \W \(\d{3}\)\s\d{3}-\d quot;,COLUMNS($A:A))

and copy/drag across two more columns (or as many as are needed).

This does assume that your phone number pattern is (nnn) nnn-nnnn

=REGEX.MID($A$1,quot;[A-Za-z] \W \(\d{3}\)\s\d{3}-\d quot;,COLUMNS($A:A))

If other patterns could be present, you must let us know.
--ron


In your WORD document - Do a TABLE-CONVERT-TEXT TO TABLE, then in the
POPUP Window in the Number of Columns - enter 3. This will put your
data into 3 separate columns. You will need a paragraph return after
each line for this to work and no extra returns between the lines.
Otherwise - you can sort the single column - this should group your
stuff and then select each group and then copy and paste to the new
columns.--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: www.excelforum.com/member.php...oamp;userid=29640
View this thread: www.excelforum.com/showthread...hreadid=521945