Remove & extract name in one cell

[Guest]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

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),FIND("MS",B1))-1)

Where B1 is the first name +MR/MS and drag down.

Mike

 

[Guest]

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(F1>0,LEFT(E1,F1-1),E1)

Middle name in H1:
=IF(F1>0,MID(E1,F1,LEN(E1)),"")

Fill down the formulae as required!

Regards,
Stefi


„Freshman†ezt írta:

 

[Guest]

Hi Stefi,

Thanks for your help. My problem is solved. Thanks again

 

[Guest]

Hi Mike,

Thanks for your assistance.

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),FIND("MS",B1))-1)

Where B1 is the first name +MR/MS and drag down.

Mike

 

[Guest]

Hi Rick,

My problem is solved. Thank you for your time. Best regards.

 

[Guest]

You are welcome! Thanks for the feedback!
Stefi


„Freshman†ezt írta: