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.