| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
combine titles/names - how?
Hi
I have titles & names is two columns in column c I have Aiken Mr R. Aiken Mrs C. Bell Mr D. Bell Mrs E. etc, etc, In column d I have Robert Carol David Elaine etc, etc How do I join the title/name to get Aiken Mr Robert etc, etc -- Martin ©¿©¬ |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
combine titles/names - how?
Hi,
In E1 and drag down =LEFT(C1,(FIND("|",SUBSTITUTE(C1," ","|",2)))-1)&" "&D1 Mike "Martin ©¿©¬ @nohere.net" wrote: Hi I have titles & names is two columns in column c I have Aiken Mr R. Aiken Mrs C. Bell Mr D. Bell Mrs E. etc, etc, In column d I have Robert Carol David Elaine etc, etc How do I join the title/name to get Aiken Mr Robert etc, etc -- Martin ©¿©¬ |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
combine titles/names - how?
Another
=LEFT(TRIM(C1),LEN(TRIM(C1))-2)&D1 Mike "Mike H" wrote: Hi, In E1 and drag down =LEFT(C1,(FIND("|",SUBSTITUTE(C1," ","|",2)))-1)&" "&D1 Mike "Martin ©¿©¬ @nohere.net" wrote: Hi I have titles & names is two columns in column c I have Aiken Mr R. Aiken Mrs C. Bell Mr D. Bell Mrs E. etc, etc, In column d I have Robert Carol David Elaine etc, etc How do I join the title/name to get Aiken Mr Robert etc, etc -- Martin ©¿©¬ |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
combine titles/names - how?
On Sat, 3 Oct 2009 05:55:01 -0700, Mike H
wrote: Hi, In E1 and drag down =LEFT(C1,(FIND("|",SUBSTITUTE(C1," ","|",2)))-1)&" "&D1 Thank you very much Mike That did the job -- Martin ©¿©¬ |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
combine titles/names - how?
Minor recommendation, I would keep the formula below but will change the -2
to -0 if you really need to display the entire name in column C. The -2 removes that number of characters from the name in C1. Other than that, this formula works great. -- tech1NJ "Mike H" wrote: Another =LEFT(TRIM(C1),LEN(TRIM(C1))-2)&D1 Mike "Mike H" wrote: Hi, In E1 and drag down =LEFT(C1,(FIND("|",SUBSTITUTE(C1," ","|",2)))-1)&" "&D1 Mike "Martin ©¿©¬ @nohere.net" wrote: Hi I have titles & names is two columns in column c I have Aiken Mr R. Aiken Mrs C. Bell Mr D. Bell Mrs E. etc, etc, In column d I have Robert Carol David Elaine etc, etc How do I join the title/name to get Aiken Mr Robert etc, etc -- Martin ©¿©¬ |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
combine titles/names - Again
When I use the formula =LEFT(TRIM(C1),LEN(TRIM(C1))-0)&D1 the names
come out as Mr & Mrs R.Aiken etc etc How would I change the formula to create Mr & Mrs R. Aiken (with a space after the period)? -- Martin ©¿©¬ On Sun, 4 Oct 2009 12:41:01 -0700, tech1NJ wrote: Minor recommendation, I would keep the formula below but will change the -2 to -0 if you really need to display the entire name in column C. The -2 removes that number of characters from the name in C1. Other than that, this formula works great. -- tech1NJ "Mike H" wrote: Another =LEFT(TRIM(C1),LEN(TRIM(C1))-2)&D1 Mike "Mike H" wrote: Hi, In E1 and drag down =LEFT(C1,(FIND("|",SUBSTITUTE(C1," ","|",2)))-1)&" "&D1 Mike "Martin ©¿©¬ @nohere.net" wrote: Hi I have titles & names is two columns in column c I have Aiken Mr R. Aiken Mrs C. Bell Mr D. Bell Mrs E. etc, etc, In column d I have Robert Carol David Elaine etc, etc How do I join the title/name to get Aiken Mr Robert etc, etc -- Martin ©¿©¬ |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
combine titles/names - Again
try this
=LEFT(TRIM(C1),LEN(TRIM(C1))-0)&" "&D1 -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Martin ©¿©¬ @nohere.net" wrote: When I use the formula =LEFT(TRIM(C1),LEN(TRIM(C1))-0)&D1 the names come out as Mr & Mrs R.Aiken etc etc How would I change the formula to create Mr & Mrs R. Aiken (with a space after the period)? -- Martin ©¿©¬ On Sun, 4 Oct 2009 12:41:01 -0700, tech1NJ wrote: Minor recommendation, I would keep the formula below but will change the -2 to -0 if you really need to display the entire name in column C. The -2 removes that number of characters from the name in C1. Other than that, this formula works great. -- tech1NJ "Mike H" wrote: Another =LEFT(TRIM(C1),LEN(TRIM(C1))-2)&D1 Mike "Mike H" wrote: Hi, In E1 and drag down =LEFT(C1,(FIND("|",SUBSTITUTE(C1," ","|",2)))-1)&" "&D1 Mike "Martin ©¿©¬ @nohere.net" wrote: Hi I have titles & names is two columns in column c I have Aiken Mr R. Aiken Mrs C. Bell Mr D. Bell Mrs E. etc, etc, In column d I have Robert Carol David Elaine etc, etc How do I join the title/name to get Aiken Mr Robert etc, etc -- Martin ©¿©¬ . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
combine titles/names - Again
On Mon, 22 Mar 2010 05:25:01 -0700, Mike H
wrote: try this =LEFT(TRIM(C1),LEN(TRIM(C1))-0)&" "&D1 Thank you Mike That did the trick -- Martin ©¿©¬ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HOW DO I COMBINE two spreadsheets into one by names in column a | Excel Discussion (Misc queries) | |||
| Column Titles/Names | Setting up and Configuration of Excel | |||
| Combine several Names in one folder with if-formula | Excel Discussion (Misc queries) | |||
| how do I combine cells (names) | Excel Discussion (Misc queries) | |||
| Combine names seperated by comma | Excel Discussion (Misc queries) | |||
Linear Mode
