Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
uncombining columns
I have a question. Someone below asked the question about combining columns
(See "Combining columns????"). I would like to know if there is a formula that would do the same but the other way around, in other words, breaking the content of a cell into separate cells. The formula provided to combine was: = A1&", "&B1&", "&C1 so I guess what I need is the opposite of what this formula does. Any thoughts? I especifically want to break down a cell that has a name and last name in the same cell and break it down into two cells so I can do a .CSV file and imported into my outlook. Thanks for the replies in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
uncombining columns
Do you have a comma and a space between the two names, or just a
space? Will you always have two names, or might there be a 3rd name (or initials)? Pete On Jan 15, 11:58*pm, Jorge wrote: I have a question. Someone below asked the question about combining columns (See "Combining columns????"). I would like to know if there is a formula that would do the same but the other way around, in other words, breaking the content of a cell into separate cells. The formula provided to combine was: = A1&", "&B1&", "&C1 so I guess what I need is the opposite of what this formula does. Any thoughts? I especifically want to break down a cell that has a name and last name in the same cell and break it down into two cells so I can do a .CSV file and imported into my outlook. Thanks for the replies in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
uncombining columns
Maybe try DataText to ColumnsDelimited by space or whatever.
Gord Dibben MS Excel MVP On Tue, 15 Jan 2008 15:58:00 -0800, Jorge wrote: I have a question. Someone below asked the question about combining columns (See "Combining columns????"). I would like to know if there is a formula that would do the same but the other way around, in other words, breaking the content of a cell into separate cells. The formula provided to combine was: = A1&", "&B1&", "&C1 so I guess what I need is the opposite of what this formula does. Any thoughts? I especifically want to break down a cell that has a name and last name in the same cell and break it down into two cells so I can do a .CSV file and imported into my outlook. Thanks for the replies in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
uncombining columns
There are sometimes one name and one last name, other times two names and a
last name and even some other times two names or a name and initial and two last names, in any case, what I want to separate is, so far, always separated by a comma. Question: Using the DataText to ColumnsDelimited by comma function would need to be used separately for each cell right? Is there a way I can select a column with 100 cells with names and last names and splitted easily into two colums with names in one column and last names in the other? Also, it hasn't been the case yet but, what if the names and last names have no commas separating them (i.e. George Walker Bush) where I have two names and just one last name - how could I choose to separate just the last name in this case? Thank you, you guys are great! "Pete_UK" wrote: Do you have a comma and a space between the two names, or just a space? Will you always have two names, or might there be a 3rd name (or initials)? Pete On Jan 15, 11:58 pm, Jorge wrote: I have a question. Someone below asked the question about combining columns (See "Combining columns????"). I would like to know if there is a formula that would do the same but the other way around, in other words, breaking the content of a cell into separate cells. The formula provided to combine was: = A1&", "&B1&", "&C1 so I guess what I need is the opposite of what this formula does. Any thoughts? I especifically want to break down a cell that has a name and last name in the same cell and break it down into two cells so I can do a .CSV file and imported into my outlook. Thanks for the replies in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
uncombining columns
You do not need to apply Data | Text-to-columns a single cell at a
time - highlight the range A1:A100 and then click on Data | Text-to- columns and follow it through. In the case where you only have spaces to separate names, and have more than two names, then you can build up a formula using FIND (or SEARCH) to look for the spaces, in conjunction with LEN and SUBSTITUTE, and use LEFT, RIGHT or MID to extract the names. Hope this helps. Pete On Jan 16, 3:46*pm, Jorge wrote: There are sometimes one name and one last name, other times two names and a last name and even some other times two names or a name and initial and two last names, in any case, what I want to separate is, so far, always separated by a comma. Question: Using the DataText to ColumnsDelimited by comma function would need to be used separately for each cell right? Is there a way I can select a column with 100 cells with names and last names and splitted easily into two colums with names in one column and last names in the other? Also, it hasn't been the case yet but, what if the names and last names have no commas separating them (i.e. George Walker Bush) where I have two names and just one last name - how could I choose to separate just the last name in this case? Thank you, you guys are great! "Pete_UK" wrote: Do you have a comma and a space between the two names, or just a space? Will you always have two names, or might there be a 3rd name (or initials)? Pete On Jan 15, 11:58 pm, Jorge wrote: I have a question. Someone below asked the question about combining columns (See "Combining columns????"). I would like to know if there is a formula that would do the same but the other way around, in other words, breaking the content of a cell into separate cells. The formula provided to combine was: = A1&", "&B1&", "&C1 so I guess what I need is the opposite of what this formula does. Any thoughts? I especifically want to break down a cell that has a name and last name in the same cell and break it down into two cells so I can do a .CSV file and imported into my outlook. Thanks for the replies in advance.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Uncombining" Text | Excel Discussion (Misc queries) | |||
to convert columns to rows having mulit independent group columns | Excel Worksheet Functions | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Pivot Table Creating New Columns that Subtract Two Existing Columns | Excel Discussion (Misc queries) |