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.

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

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.

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.

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.

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.

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

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.