Separate text within a cell

[Angela]

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?

[Paul Lautman]

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

[Angela]

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

[Dominic]

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

[Ron Rosenfeld]

On Mon, 13 Mar 2006 13:46:27 -0800, Angela <[email protected]>
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

[wjohnson]

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.