| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trim a cell that has names and address to only show name
I have quite a few cells that have alot of information in them eg surname,
christian name and address. I only need the surname and christian name in one cell. How can I do this? eg Smith Jones 123 Harper St Manly (A1). I need the cell to only show Smith Jones (B1) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim a cell that has names and address to only show name
Lengthy, but it always returns the second word from the string searched:
=MID(MID(MID(SUBSTITUTE(A1," ","^",1),1,256), FIND("^",SUBSTITUTE(A1," ","^",1)),256),2, FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",1),1,256), FIND("^",SUBSTITUTE(A1," ","^",1)),256))-2) Whichever word you want in the string, change the single digit in the SUBSTITUTE to that number -1. So to get the 3rd word, change all the 1s to 2s. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Christie" wrote: I have quite a few cells that have alot of information in them eg surname, christian name and address. I only need the surname and christian name in one cell. How can I do this? eg Smith Jones 123 Harper St Manly (A1). I need the cell to only show Smith Jones (B1) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim a cell that has names and address to only show name
Just to be clear about changing it if you want to for other purposes, here is
the version that would always return the FOURTH word: =MID(MID(MID(SUBSTITUTE(A1," ","^",3),1,256), FIND("^",SUBSTITUTE(A1," ","^",3)),256),2, FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",3),1,256), FIND("^",SUBSTITUTE(A1," ","^",3)),256))-2) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: Lengthy, but it always returns the second word from the string searched: =MID(MID(MID(SUBSTITUTE(A1," ","^",1),1,256), FIND("^",SUBSTITUTE(A1," ","^",1)),256),2, FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",1),1,256), FIND("^",SUBSTITUTE(A1," ","^",1)),256))-2) Whichever word you want in the string, change the single digit in the SUBSTITUTE to that number -1. So to get the 3rd word, change all the 1s to 2s. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Christie" wrote: I have quite a few cells that have alot of information in them eg surname, christian name and address. I only need the surname and christian name in one cell. How can I do this? eg Smith Jones 123 Harper St Manly (A1). I need the cell to only show Smith Jones (B1) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim a cell that has names and address to only show name
This works really well, thank you, however I need to also grab the first word
of the cell. I have tried 0 and -1 but that doesn't seem to work. Can you help with this one? "JBeaucaire" wrote: Just to be clear about changing it if you want to for other purposes, here is the version that would always return the FOURTH word: =MID(MID(MID(SUBSTITUTE(A1," ","^",3),1,256), FIND("^",SUBSTITUTE(A1," ","^",3)),256),2, FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",3),1,256), FIND("^",SUBSTITUTE(A1," ","^",3)),256))-2) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: Lengthy, but it always returns the second word from the string searched: =MID(MID(MID(SUBSTITUTE(A1," ","^",1),1,256), FIND("^",SUBSTITUTE(A1," ","^",1)),256),2, FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",1),1,256), FIND("^",SUBSTITUTE(A1," ","^",1)),256))-2) Whichever word you want in the string, change the single digit in the SUBSTITUTE to that number -1. So to get the 3rd word, change all the 1s to 2s. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Christie" wrote: I have quite a few cells that have alot of information in them eg surname, christian name and address. I only need the surname and christian name in one cell. How can I do this? eg Smith Jones 123 Harper St Manly (A1). I need the cell to only show Smith Jones (B1) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim a cell that has names and address to only show name
Assuming the text after the full name *always* starts with a number (if it
doesn't, it will be nearly impossible to tell if the third "word" is part of the name or is a street name without a number)... =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-2) If there might be more than one space between the name and house number, then use this instead.. =TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))-1)) -- Rick (MVP - Excel) "Christie" wrote in message ... I have quite a few cells that have alot of information in them eg surname, christian name and address. I only need the surname and christian name in one cell. How can I do this? eg Smith Jones 123 Harper St Manly (A1). I need the cell to only show Smith Jones (B1) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim a cell that has names and address to only show name
First word is easy:
=LEFT(A1,FIND(" ",A1)-1) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Christie" wrote: This works really well, thank you, however I need to also grab the first word of the cell. I have tried 0 and -1 but that doesn't seem to work. Can you help with this one? "JBeaucaire" wrote: Just to be clear about changing it if you want to for other purposes, here is the version that would always return the FOURTH word: =MID(MID(MID(SUBSTITUTE(A1," ","^",3),1,256), FIND("^",SUBSTITUTE(A1," ","^",3)),256),2, FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",3),1,256), FIND("^",SUBSTITUTE(A1," ","^",3)),256))-2) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim a cell that has names and address to only show name
I have two questions for you...
1) What if the person has 3 names? I have two examples of this from friends of mine at work. The first is a woman whose legal first name is Mary Anne? That coupled with her last name would give her 3 names on your list. The second is a man whose last name if Della Rossa. That coupled with his first name would give you 3 names on your list. How would you handle these cases with the formulas JBeaucaire has given you? 2) Have you seen the formula I posted elsewhere in this thread? -- Rick (MVP - Excel) "Christie" wrote in message ... This works really well, thank you, however I need to also grab the first word of the cell. I have tried 0 and -1 but that doesn't seem to work. Can you help with this one? "JBeaucaire" wrote: Just to be clear about changing it if you want to for other purposes, here is the version that would always return the FOURTH word: =MID(MID(MID(SUBSTITUTE(A1," ","^",3),1,256), FIND("^",SUBSTITUTE(A1," ","^",3)),256),2, FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",3),1,256), FIND("^",SUBSTITUTE(A1," ","^",3)),256))-2) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: Lengthy, but it always returns the second word from the string searched: =MID(MID(MID(SUBSTITUTE(A1," ","^",1),1,256), FIND("^",SUBSTITUTE(A1," ","^",1)),256),2, FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",1),1,256), FIND("^",SUBSTITUTE(A1," ","^",1)),256))-2) Whichever word you want in the string, change the single digit in the SUBSTITUTE to that number -1. So to get the 3rd word, change all the 1s to 2s. -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Christie" wrote: I have quite a few cells that have alot of information in them eg surname, christian name and address. I only need the surname and christian name in one cell. How can I do this? eg Smith Jones 123 Harper St Manly (A1). I need the cell to only show Smith Jones (B1) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim a cell that has names and address to only show name
Thank you
Now that I have grabbed the name and the address into seperate cells, I neeed to see if they match the other spreadsheet. The name matches but now I net to see if the address also matches the name. "JBeaucaire" wrote: First word is easy: =LEFT(A1,FIND(" ",A1)-1) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "Christie" wrote: This works really well, thank you, however I need to also grab the first word of the cell. I have tried 0 and -1 but that doesn't seem to work. Can you help with this one? "JBeaucaire" wrote: Just to be clear about changing it if you want to for other purposes, here is the version that would always return the FOURTH word: =MID(MID(MID(SUBSTITUTE(A1," ","^",3),1,256), FIND("^",SUBSTITUTE(A1," ","^",3)),256),2, FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",3),1,256), FIND("^",SUBSTITUTE(A1," ","^",3)),256))-2) -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Trim a cell that has names and address to only show name
Thank you now I need to match this and see if the address match the other
spreadsheet including the names aswell. How do I do this? "Rick Rothstein" wrote: Assuming the text after the full name *always* starts with a number (if it doesn't, it will be nearly impossible to tell if the third "word" is part of the name or is a street name without a number)... =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-2) If there might be more than one space between the name and house number, then use this instead.. =TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))-1)) -- Rick (MVP - Excel) "Christie" wrote in message ... I have quite a few cells that have alot of information in them eg surname, christian name and address. I only need the surname and christian name in one cell. How can I do this? eg Smith Jones 123 Harper St Manly (A1). I need the cell to only show Smith Jones (B1) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to create table of cell names with the name's cell address | Excel Discussion (Misc queries) | |||
| How do I show the address of hyperlink to another cell in Excel ? | Excel Discussion (Misc queries) | |||
| address help (similar to trim or transpose) | Excel Worksheet Functions | |||
| add email address to a list of names | Excel Discussion (Misc queries) | |||
| HOW DO I ENTER TWO NAMES IN ONE ROW FOR ONE ADDRESS FOR LABELS | Excel Discussion (Misc queries) | |||
Linear Mode
