splitting contents of a cell

  • Thread starter Thread starter april
  • Start date Start date
A

april

i have many (over 8000) cells with names and i want to isolate the last name.
the problem is that the format varies. Possible formats

1. A. Sharp
2. Allison Sharp
3. Dr. Allison Sharp
4. Dr and Mr Allison sharp
5. Capt. Allison Sharp, USN, Ret

i could give more examples, but i think that you get the idea. i have tried
text to columns but because there is not pattern this isn't very efficient.

thanks in advance for your help
 
Here's one way that Biff posted about a year ago:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

with your text in A1.

Hope this helps.

Pete
 
Try the below in cell B1 with data in cell A1 and copy down as required. The
below formula would split the cells with comma to pick the first element and
then extract the last word.

=TRIM(RIGHT(SUBSTITUTE(IF(ISNUMBER(FIND(",",A1)),
REPLACE(A1,FIND(",",A1),99,""),A1)," ",REPT(" ",255)),255))
 
i have many (over 8000) cells with names and i want to isolate the last name.
the problem is that the format varies. Possible formats

1. A. Sharp
2. Allison Sharp
3. Dr. Allison Sharp
4. Dr and Mr Allison sharp
5. Capt. Allison Sharp, USN, Ret

i could give more examples, but i think that you get the idea. i have tried
text to columns but because there is not pattern this isn't very efficient.

thanks in advance for your help
Click to expand...

From what you post, it appears that the last name is either the last word in
the string, or it is the first word that is followed by a comma.

That being the case:

=TRIM(RIGHT(SUBSTITUTE(LEFT(TRIM(A1)&",",
FIND(",",TRIM(A1)&",")-1)," ",REPT(" ",99)),99))

--ron
 
Unfortunately none of these solutions work. Jacob and Pete, your formulas
give me the last character of the string. for instance, if the string was
Dr. Tom Feelgood M.D., your formulas returned "."

Ron, i was mistaken in my examples. there is no "," in the string. i
believe that i gave an example of Capt. John Smith, USN, Ret. Instead the
string reads Capt. John Smith USN (ret).

thanks for the help though. any more ideas?

thanks in advance
 
Ron, i was mistaken in my examples. there is no "," in the string. i
believe that i gave an example of Capt. John Smith, USN, Ret. Instead the
string reads Capt. John Smith USN (ret).

thanks for the help though. any more ideas?

thanks in advance
Click to expand...

You can do this with a User Defined Function if you can list all the possible
suffixes.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

You will need to add other possible suffixes to the pipe-delimited list in the
UDF.

To use this User Defined Function (UDF), enter a formula like
=LastName(cell_ref)
in some cell.

=============================================
Option Explicit
Function LastName(s As String) As String
Dim Re As Object

'add other possible suffixes as part of the pipe-delimited list
'Only the first word of multi-word suffixes is needed
Const Suffixes As String = "USN|MD|M.D."

Set Re = CreateObject("vbscript.regexp")
Re.Global = True
Re.Pattern = "^.*?\s([\-\w]+)\s*(?=,\s*|" & Suffixes & "|$).*"
LastName = Re.Replace(s, "$1")
End Function
===========================================
--ron
 
Back
Top