Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I make an excel cell equal to the value of a frame object text box | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
How do I set text to top of cell next to wrap text in Excel? | New Users to Excel | |||
Possible Lookup Table | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |