| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting data in multiple cells
I need help splitting names out of a cell into two. The problem I am having
is there are some cells with the first middle initial and last name where some have just the first and last. Below is an example of what I have and what I want to do. Currently A 1 SCOTT BRANDON 2 NEWMAN J ROBERT 3 HENDRIC A ALLAN 4 HENDRIC ALLAN What I want A B 1 SCOTT BRANDON 2 NEWMAN J ROBERT 3 HENDRIC A ALLAN 4 HENDRIC ALLAN |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Splitting data in multiple cells
On Thu, 30 Oct 2008 06:42:10 -0700, Jeremy
wrote: I need help splitting names out of a cell into two. The problem I am having is there are some cells with the first middle initial and last name where some have just the first and last. Below is an example of what I have and what I want to do. Currently A 1 SCOTT BRANDON 2 NEWMAN J ROBERT 3 HENDRIC A ALLAN 4 HENDRIC ALLAN What I want A B 1 SCOTT BRANDON 2 NEWMAN J ROBERT 3 HENDRIC A ALLAN 4 HENDRIC ALLAN Here's one method. It involves extracting the Last Name (last word in the string), and then removing that Last Name from the original. B1: =TRIM(SUBSTITUTE(A1,C1,"",1)) C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) Note that B1 will display the full name until you enter the formula into C1. Fill down as required. If all looks well, you can Select B1:Cn Edit/Copy Select A1 Paste Special Values which will replace the contents of column A as you request. --ron |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Splitting data in multiple cells
"Ron Rosenfeld" wrote: On Thu, 30 Oct 2008 06:42:10 -0700, Jeremy wrote: I need help splitting names out of a cell into two. The problem I am having is there are some cells with the first middle initial and last name where some have just the first and last. Below is an example of what I have and what I want to do. Currently A 1 SCOTT BRANDON 2 NEWMAN J ROBERT 3 HENDRIC A ALLAN 4 HENDRIC ALLAN What I want A B 1 SCOTT BRANDON 2 NEWMAN J ROBERT 3 HENDRIC A ALLAN 4 HENDRIC ALLAN Here's one method. It involves extracting the Last Name (last word in the string), and then removing that Last Name from the original. B1: =TRIM(SUBSTITUTE(A1,C1,"",1)) C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) Note that B1 will display the full name until you enter the formula into C1. Fill down as required. If all looks well, you can Select B1:Cn Edit/Copy Select A1 Paste Special Values which will replace the contents of column A as you request. --ron This didn't work. It gave me zeros |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Splitting data in multiple cells
On Thu, 30 Oct 2008 08:08:28 -0700, Jeremy wrote: "Ron Rosenfeld" wrote: On Thu, 30 Oct 2008 06:42:10 -0700, Jeremy wrote: I need help splitting names out of a cell into two. The problem I am having is there are some cells with the first middle initial and last name where some have just the first and last. Below is an example of what I have and what I want to do. Currently A 1 SCOTT BRANDON 2 NEWMAN J ROBERT 3 HENDRIC A ALLAN 4 HENDRIC ALLAN What I want A B 1 SCOTT BRANDON 2 NEWMAN J ROBERT 3 HENDRIC A ALLAN 4 HENDRIC ALLAN Here's one method. It involves extracting the Last Name (last word in the string), and then removing that Last Name from the original. B1: =TRIM(SUBSTITUTE(A1,C1,"",1)) C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) Note that B1 will display the full name until you enter the formula into C1. Fill down as required. If all looks well, you can Select B1:Cn Edit/Copy Select A1 Paste Special Values which will replace the contents of column A as you request. --ron This didn't work. It gave me zeros Before or after you went through the copy | paste special values routine? Did you copy and then paste the formulas into the appropriate cells? Or did you possibly have a typo when you tried to "type" them into the target cell? If both formulas are returning zeros, and you copy/pasted them correctly into your worksheet, then your source cells contain zeros. If your source cells were empty, the formulas return a null string. Provide some more data, please. --ron |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Splitting data in multiple cells
I see what you are having me do now. The problem with this is that I can not
have column a in my sheet now when this is done. If a is deleted the data in b and c will go away. This has to be split in two rows and not have a source they are pulling from. Text to column would be nice if it left the middle name or initial with the first and only moved the last name to b. "Ron Rosenfeld" wrote: On Thu, 30 Oct 2008 08:08:28 -0700, Jeremy wrote: "Ron Rosenfeld" wrote: On Thu, 30 Oct 2008 06:42:10 -0700, Jeremy wrote: I need help splitting names out of a cell into two. The problem I am having is there are some cells with the first middle initial and last name where some have just the first and last. Below is an example of what I have and what I want to do. Currently A 1 SCOTT BRANDON 2 NEWMAN J ROBERT 3 HENDRIC A ALLAN 4 HENDRIC ALLAN What I want A B 1 SCOTT BRANDON 2 NEWMAN J ROBERT 3 HENDRIC A ALLAN 4 HENDRIC ALLAN Here's one method. It involves extracting the Last Name (last word in the string), and then removing that Last Name from the original. B1: =TRIM(SUBSTITUTE(A1,C1,"",1)) C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) Note that B1 will display the full name until you enter the formula into C1. Fill down as required. If all looks well, you can Select B1:Cn Edit/Copy Select A1 Paste Special Values which will replace the contents of column A as you request. --ron This didn't work. It gave me zeros Before or after you went through the copy | paste special values routine? Did you copy and then paste the formulas into the appropriate cells? Or did you possibly have a typo when you tried to "type" them into the target cell? If both formulas are returning zeros, and you copy/pasted them correctly into your worksheet, then your source cells contain zeros. If your source cells were empty, the formulas return a null string. Provide some more data, please. --ron |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Splitting data in multiple cells
On Thu, 30 Oct 2008 08:44:01 -0700, Jeremy
wrote: I see what you are having me do now. The problem with this is that I can not have column a in my sheet now when this is done. If a is deleted the data in b and c will go away. This has to be split in two rows and not have a source they are pulling from. Text to column would be nice if it left the middle name or initial with the first and only moved the last name to b. I took that issue into account in my response. Probably you did not follow my instructions the way I expected. Please post the exact steps you took so we can see what the problem is. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reverse Concatenation (Splitting Single Cell Data into Multiple Ce | Excel Discussion (Misc queries) | |||
| Splitting data into multiple fields. | Excel Worksheet Functions | |||
| Splitting data in cells | Excel Discussion (Misc queries) | |||
| Where is the Data Tools group Help refers to when splitting cells | Excel Worksheet Functions | |||
| splitting 1 column of data into multiple columns | Setting up and Configuration of Excel | |||
Linear Mode
