Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove & extract name in one cell
Dear experts,
I've a table of data which column A is for names. However, the name details of each client (first, last and Mr...) imported from other source files are in one word, such as: Freshman/TimCruzMR Peter/JonesMR Lily/ElizaAkotMS In the above examples, firstly, I want to remove the title "MR", "MS". Secondly, I want to separate the names in one cell: Freshman Tim Cruz Peter Jones Lily Eliza Akot Please advise how can I achieve my desired results. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove & extract name in one cell
I've a table of data which column A is for names. However, the name
details of each client (first, last and Mr...) imported from other source files are in one word, such as: Freshman/TimCruzMR Peter/JonesMR Lily/ElizaAkotMS In the above examples, firstly, I want to remove the title "MR", "MS". Secondly, I want to separate the names in one cell: Freshman Tim Cruz Peter Jones Lily Eliza Akot Please advise how can I achieve my desired results. A few questions. Is MR and MS the only possible endings? If not, what are the others? Is it possible for the text not to have an MR, MS, etc. ending? Is the capitalization as shown (the MR, MS, etc. is always upper case and the letter in front of it is lower case)? Are you wanting to do the text manipulations in place or do you have the table in another location and you are referencing the cells in the table in Column A? In names like Lily/ElizaAkot... there is no slash character between the middle name and the last name??? Rick |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove & extract name in one cell
A couple of stages required.
To seperate first and last name select the column and then Data|text to coluns and follow the wizard. You will need to select a delimeter of / This will leave the first name and MR/MS in a new column. In an adjacent coulmn type the formula:- =LEFT(B1,IF(ISERROR(FIND("MS",B1)),FIND("MR",B1),F IND("MS",B1))-1) Where B1 is the first name +MR/MS and drag down. Mike "Freshman" wrote: Dear experts, I've a table of data which column A is for names. However, the name details of each client (first, last and Mr...) imported from other source files are in one word, such as: Freshman/TimCruzMR Peter/JonesMR Lily/ElizaAkotMS In the above examples, firstly, I want to remove the title "MR", "MS". Secondly, I want to separate the names in one cell: Freshman Tim Cruz Peter Jones Lily Eliza Akot Please advise how can I achieve my desired results. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove & extract name in one cell
Provided that names always end with MR or MS, first step is to cut them off,
in B1: =LEFT(A1,LEN(A1)-2) Finding /, in C1: =SEARCH("/",A1) Last neme, in D1: =LEFT(B1,C1-1) First and middle name, in E1: =MID(B1,C1+1,LEN(B1)) UDF for finding second capital in E1, in F1: =cappos(E1,2) Function CapPos(txtvalue, whichcap) txtlen = Len(txtvalue) capcount = 0 CapPos = 0 For i = 1 To txtlen If Asc(Mid(txtvalue, i, 1)) = 65 And Asc(Mid(txtvalue, i, 1)) <= 90 Then capcount = capcount + 1 End If If capcount = whichcap Then CapPos = i Exit For End If Next i End Function First name in G1: =IF(F10,LEFT(E1,F1-1),E1) Middle name in H1: =IF(F10,MID(E1,F1,LEN(E1)),"") Fill down the formulae as required! Regards, Stefi €˛Freshman€¯ ezt Ć*rta: Dear experts, I've a table of data which column A is for names. However, the name details of each client (first, last and Mr...) imported from other source files are in one word, such as: Freshman/TimCruzMR Peter/JonesMR Lily/ElizaAkotMS In the above examples, firstly, I want to remove the title "MR", "MS". Secondly, I want to separate the names in one cell: Freshman Tim Cruz Peter Jones Lily Eliza Akot Please advise how can I achieve my desired results. Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove & extract name in one cell
Hi Stefi,
Thanks for your help. My problem is solved. Thanks again "Stefi" wrote: Provided that names always end with MR or MS, first step is to cut them off, in B1: =LEFT(A1,LEN(A1)-2) Finding /, in C1: =SEARCH("/",A1) Last neme, in D1: =LEFT(B1,C1-1) First and middle name, in E1: =MID(B1,C1+1,LEN(B1)) UDF for finding second capital in E1, in F1: =cappos(E1,2) Function CapPos(txtvalue, whichcap) txtlen = Len(txtvalue) capcount = 0 CapPos = 0 For i = 1 To txtlen If Asc(Mid(txtvalue, i, 1)) = 65 And Asc(Mid(txtvalue, i, 1)) <= 90 Then capcount = capcount + 1 End If If capcount = whichcap Then CapPos = i Exit For End If Next i End Function First name in G1: =IF(F10,LEFT(E1,F1-1),E1) Middle name in H1: =IF(F10,MID(E1,F1,LEN(E1)),"") Fill down the formulae as required! Regards, Stefi €˛Freshman€¯ ezt Ć*rta: Dear experts, I've a table of data which column A is for names. However, the name details of each client (first, last and Mr...) imported from other source files are in one word, such as: Freshman/TimCruzMR Peter/JonesMR Lily/ElizaAkotMS In the above examples, firstly, I want to remove the title "MR", "MS". Secondly, I want to separate the names in one cell: Freshman Tim Cruz Peter Jones Lily Eliza Akot Please advise how can I achieve my desired results. Thanks in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove & extract name in one cell
Hi Mike,
Thanks for your assistance. "Mike H" wrote: A couple of stages required. To seperate first and last name select the column and then Data|text to coluns and follow the wizard. You will need to select a delimeter of / This will leave the first name and MR/MS in a new column. In an adjacent coulmn type the formula:- =LEFT(B1,IF(ISERROR(FIND("MS",B1)),FIND("MR",B1),F IND("MS",B1))-1) Where B1 is the first name +MR/MS and drag down. Mike "Freshman" wrote: Dear experts, I've a table of data which column A is for names. However, the name details of each client (first, last and Mr...) imported from other source files are in one word, such as: Freshman/TimCruzMR Peter/JonesMR Lily/ElizaAkotMS In the above examples, firstly, I want to remove the title "MR", "MS". Secondly, I want to separate the names in one cell: Freshman Tim Cruz Peter Jones Lily Eliza Akot Please advise how can I achieve my desired results. Thanks in advance. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove & extract name in one cell
Hi Rick,
My problem is solved. Thank you for your time. Best regards. "Rick Rothstein (MVP - VB)" wrote: I've a table of data which column A is for names. However, the name details of each client (first, last and Mr...) imported from other source files are in one word, such as: Freshman/TimCruzMR Peter/JonesMR Lily/ElizaAkotMS In the above examples, firstly, I want to remove the title "MR", "MS". Secondly, I want to separate the names in one cell: Freshman Tim Cruz Peter Jones Lily Eliza Akot Please advise how can I achieve my desired results. A few questions. Is MR and MS the only possible endings? If not, what are the others? Is it possible for the text not to have an MR, MS, etc. ending? Is the capitalization as shown (the MR, MS, etc. is always upper case and the letter in front of it is lower case)? Are you wanting to do the text manipulations in place or do you have the table in another location and you are referencing the cells in the table in Column A? In names like Lily/ElizaAkot... there is no slash character between the middle name and the last name??? Rick |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove & extract name in one cell
You are welcome! Thanks for the feedback!
Stefi €˛Freshman€¯ ezt Ć*rta: Hi Stefi, Thanks for your help. My problem is solved. Thanks again "Stefi" wrote: Provided that names always end with MR or MS, first step is to cut them off, in B1: =LEFT(A1,LEN(A1)-2) Finding /, in C1: =SEARCH("/",A1) Last neme, in D1: =LEFT(B1,C1-1) First and middle name, in E1: =MID(B1,C1+1,LEN(B1)) UDF for finding second capital in E1, in F1: =cappos(E1,2) Function CapPos(txtvalue, whichcap) txtlen = Len(txtvalue) capcount = 0 CapPos = 0 For i = 1 To txtlen If Asc(Mid(txtvalue, i, 1)) = 65 And Asc(Mid(txtvalue, i, 1)) <= 90 Then capcount = capcount + 1 End If If capcount = whichcap Then CapPos = i Exit For End If Next i End Function First name in G1: =IF(F10,LEFT(E1,F1-1),E1) Middle name in H1: =IF(F10,MID(E1,F1,LEN(E1)),"") Fill down the formulae as required! Regards, Stefi €˛Freshman€¯ ezt Ć*rta: Dear experts, I've a table of data which column A is for names. However, the name details of each client (first, last and Mr...) imported from other source files are in one word, such as: Freshman/TimCruzMR Peter/JonesMR Lily/ElizaAkotMS In the above examples, firstly, I want to remove the title "MR", "MS". Secondly, I want to separate the names in one cell: Freshman Tim Cruz Peter Jones Lily Eliza Akot Please advise how can I achieve my desired results. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Column Letter from Cell Reference in another Cell | Excel Discussion (Misc queries) | |||
Extract within a cell | Excel Worksheet Functions | |||
Extract From Cell | Excel Worksheet Functions | |||
how to extract data from a cell in a formula in another cell | Excel Worksheet Functions | |||
Extract Unique Values, Then Extract Again to Remove Suffixes | Excel Discussion (Misc queries) |