| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Text Functions,
Hello, Can someone help me solve this problem. I have a mailing list
consisting of single cell names (first <could be initials, Middle, Last, and a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate the names into two columns, A (Separate to) B c A1 A Francisco Madrigal A Francisco Madrigal A2 A J Benning A J Benning A3 Aaron Isaac Laine Aaron Isaac Laine A4 Abraham A. Abreu Sr Abraham A. Abreu Sr I have tried making several formulas but so far I have not been able to get the separated into the two columms. -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
|
#2
|
|||
|
|||
|
You can try =Left(x,x) and =right. These split out the left hand or right
hand characters. You can try different combos of these to get the columns split. A bit of manual tidying may be required to finish off. -- Peter London, UK "Lowell" wrote: Hello, Can someone help me solve this problem. I have a mailing list consisting of single cell names (first <could be initials, Middle, Last, and a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate the names into two columns, A (Separate to) B c A1 A Francisco Madrigal A Francisco Madrigal A2 A J Benning A J Benning A3 Aaron Isaac Laine Aaron Isaac Laine A4 Abraham A. Abreu Sr Abraham A. Abreu Sr I have tried making several formulas but so far I have not been able to get the separated into the two columms. -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
|
#3
|
|||
|
|||
|
Thanks for the advise, I tried what you suggested and I cannot get it to work.
-- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida "Peter" wrote: You can try =Left(x,x) and =right. These split out the left hand or right hand characters. You can try different combos of these to get the columns split. A bit of manual tidying may be required to finish off. -- Peter London, UK "Lowell" wrote: Hello, Can someone help me solve this problem. I have a mailing list consisting of single cell names (first <could be initials, Middle, Last, and a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate the names into two columns, A (Separate to) B c A1 A Francisco Madrigal A Francisco Madrigal A2 A J Benning A J Benning A3 Aaron Isaac Laine Aaron Isaac Laine A4 Abraham A. Abreu Sr Abraham A. Abreu Sr I have tried making several formulas but so far I have not been able to get the separated into the two columms. -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
|
#4
|
|||
|
|||
|
Hi,
Try this. If the name is entered in cell A6, then enter the following array formula (Ctrl+Shift+Enter) in cell B6 to get the first name MID(A6,MATCH(TRUE,EXACT(MID(A6,ROW($1:$14),1),PROP ER(MID(A6,ROW($1:$14),1))),0),MATCH(TRUE,EXACT(MID (A6,ROW($2:$14),1),PROPER(MID(A6,ROW($2:$14),1))), 0)) Enter the following array formula (Ctrl+Shift+Enter) in cell C6 to get the second and last name MID(A6,MATCH(TRUE,EXACT(MID(A6,ROW($2:$14),1),PROP ER(MID(A6,ROW($2:$14),1))),0)+1,255) Regards, Ashish Mathur "Lowell" wrote: Hello, Can someone help me solve this problem. I have a mailing list consisting of single cell names (first <could be initials, Middle, Last, and a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate the names into two columns, A (Separate to) B c A1 A Francisco Madrigal A Francisco Madrigal A2 A J Benning A J Benning A3 Aaron Isaac Laine Aaron Isaac Laine A4 Abraham A. Abreu Sr Abraham A. Abreu Sr I have tried making several formulas but so far I have not been able to get the separated into the two columms. -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
|
#5
|
|||
|
|||
|
Hi!
Those formulas are just separating everything to the left of the first space and everything to the right of the first space. These formulas will do the same thing: =LEFT(A6,FIND(" ",A6)-1) =MID(A6,FIND(" ",A6)+1,255) There's really no good way to do this! Too many variables to consider. Billy Bob Michael Van Patton Sr. Bill Gates Where does the first name end and the middle name begin in the first example? Where does the middle name end and the last name begin? It's easy to "see" but try writing a formula to do it and cover all the possibilities. Biff "Ashish Mathur" wrote in message ... Hi, Try this. If the name is entered in cell A6, then enter the following array formula (Ctrl+Shift+Enter) in cell B6 to get the first name MID(A6,MATCH(TRUE,EXACT(MID(A6,ROW($1:$14),1),PROP ER(MID(A6,ROW($1:$14),1))),0),MATCH(TRUE,EXACT(MID (A6,ROW($2:$14),1),PROPER(MID(A6,ROW($2:$14),1))), 0)) Enter the following array formula (Ctrl+Shift+Enter) in cell C6 to get the second and last name MID(A6,MATCH(TRUE,EXACT(MID(A6,ROW($2:$14),1),PROP ER(MID(A6,ROW($2:$14),1))),0)+1,255) Regards, Ashish Mathur "Lowell" wrote: Hello, Can someone help me solve this problem. I have a mailing list consisting of single cell names (first <could be initials, Middle, Last, and a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate the names into two columns, A (Separate to) B c A1 A Francisco Madrigal A Francisco Madrigal A2 A J Benning A J Benning A3 Aaron Isaac Laine Aaron Isaac Laine A4 Abraham A. Abreu Sr Abraham A. Abreu Sr I have tried making several formulas but so far I have not been able to get the separated into the two columms. -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
|
#6
|
|||
|
|||
|
Happen to come across this today...
http://www.add-ins.com/name_splitter.htm Is it what you're looking for? -- Peter London, UK "Peter" wrote: You can try =Left(x,x) and =right. These split out the left hand or right hand characters. You can try different combos of these to get the columns split. A bit of manual tidying may be required to finish off. -- Peter London, UK "Lowell" wrote: Hello, Can someone help me solve this problem. I have a mailing list consisting of single cell names (first <could be initials, Middle, Last, and a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate the names into two columns, A (Separate to) B c A1 A Francisco Madrigal A Francisco Madrigal A2 A J Benning A J Benning A3 Aaron Isaac Laine Aaron Isaac Laine A4 Abraham A. Abreu Sr Abraham A. Abreu Sr I have tried making several formulas but so far I have not been able to get the separated into the two columms. -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
|
#7
|
|||
|
|||
|
Happen to come across this today...
http://www.add-ins.com/name_splitter.htm Is it what you're looking for? -- Peter London, UK -- Peter London, UK "Lowell" wrote: Hello, Can someone help me solve this problem. I have a mailing list consisting of single cell names (first <could be initials, Middle, Last, and a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate the names into two columns, A (Separate to) B c A1 A Francisco Madrigal A Francisco Madrigal A2 A J Benning A J Benning A3 Aaron Isaac Laine Aaron Isaac Laine A4 Abraham A. Abreu Sr Abraham A. Abreu Sr I have tried making several formulas but so far I have not been able to get the separated into the two columms. -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
|
#8
|
|||
|
|||
|
Peter,
Thanks. This is exactly what I am looking for. What part of London are you from? my wife and I used to live in Chelmsford. Regards, Lowell -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida "Peter" wrote: Happen to come across this today... http://www.add-ins.com/name_splitter.htm Is it what you're looking for? -- Peter London, UK -- Peter London, UK "Lowell" wrote: Hello, Can someone help me solve this problem. I have a mailing list consisting of single cell names (first <could be initials, Middle, Last, and a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate the names into two columns, A (Separate to) B c A1 A Francisco Madrigal A Francisco Madrigal A2 A J Benning A J Benning A3 Aaron Isaac Laine Aaron Isaac Laine A4 Abraham A. Abreu Sr Abraham A. Abreu Sr I have tried making several formulas but so far I have not been able to get the separated into the two columms. -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
|
#9
|
|||
|
|||
|
The other side of town - Ealing. Glad to have helped. Nice to work with you!
-- Peter London, UK "Lowell" wrote: Peter, Thanks. This is exactly what I am looking for. What part of London are you from? my wife and I used to live in Chelmsford. Regards, Lowell -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida "Peter" wrote: Happen to come across this today... http://www.add-ins.com/name_splitter.htm Is it what you're looking for? -- Peter London, UK -- Peter London, UK "Lowell" wrote: Hello, Can someone help me solve this problem. I have a mailing list consisting of single cell names (first <could be initials, Middle, Last, and a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate the names into two columns, A (Separate to) B c A1 A Francisco Madrigal A Francisco Madrigal A2 A J Benning A J Benning A3 Aaron Isaac Laine Aaron Isaac Laine A4 Abraham A. Abreu Sr Abraham A. Abreu Sr I have tried making several formulas but so far I have not been able to get the separated into the two columms. -- Lowell Shoaf Realtor RE/MAX Gulfstream Realty Sarasota, Florida |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Resizing row height to dynamically fit height of text box | Excel Discussion (Misc queries) | |||
| Text Wrapping | Excel Discussion (Misc queries) | |||
| Create a function to return text if two logical functions are true | Excel Worksheet Functions | |||
| Autofitting a row | Excel Discussion (Misc queries) | |||
| Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Linear Mode
