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

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

When I copy it to a Word Document, it looks like this:
"Bureau: (212) 586-2000
Direct: (212) 830-2502
Outlet: (212) 586-2000
"
How can I put these in separate columns?


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("Direct",A1)-1)
=MID(A1,FIND("Direct",A1),13+9)
=MID(A1,FIND("Outlet",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
http://xcell05.free.fr

Then use the formula:

=REGEX.MID(REGEX.SUBSTITUTE($A$1,"\n"),"[[:alpha:]]+\W+\(\d{3}\)\s\d{3}-\d+",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,"[A-Za-z]+\W+\(\d{3}\)\s\d{3}-\d+",COLUMNS($A:A))

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