Separate text within a cell
 

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?

Separate text within a cell
 

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?

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(" ",A1)-1)
=MID(A1,FIND(" ",A1)+1,LEN(A1))

Separate text within a cell
 

Well - In the column it looks like this "Bureau: (212) 320-3750Direct: (212)
320-3624Outlet: (212) 320-3750 and when I tried that formula it just deleted
evverything except the first word 'Bureau"

"Paul Lautman" wrote:

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?

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(" ",A1)-1)
=MID(A1,FIND(" ",A1)+1,LEN(A1))

Separate text within a cell
 

Angela,

Try "Data:Text to Columns"

HTH


"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?

Separate text within a cell
 

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

Separate text within a cell
 

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: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=521945