splitting contents of a cell

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

-- 
aprilshowers
0
Utf
12/21/2009 5:21:01 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
521 Views

Similar Articles

[PageSpeed] 25

Hi,

=TRIM(RIGHT(A1,FIND(" ",A1)+2))

if this helps please click yes thanks

"april" wrote:

> 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
> 
> -- 
> aprilshowers
0
Utf
12/21/2009 5:31:01 PM
Here's one way that Biff posted about a year ago:

=3DTRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

with your text in A1.

Hope this helps.

Pete

On Dec 21, 5:21=A0pm, april <ap...@discussions.microsoft.com> wrote:
> i have many (over 8000) cells with names and i want to isolate the last n=
ame.
> =A0the problem is that the format varies. =A0Possible formats
>
> 1. =A0A. Sharp
> 2. =A0Allison Sharp
> 3. =A0Dr. Allison Sharp
> 4. =A0Dr and Mr Allison sharp
> 5. =A0Capt. Allison Sharp, USN, Ret
>
> i could give more examples, but i think that you get the idea. =A0i have =
tried
> text to columns but because there is not pattern this isn't very efficien=
t.
>
> thanks in advance for your help
>
> --
> aprilshowers

0
Pete_UK
12/21/2009 5:38:20 PM
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))

-- 
Jacob


"april" wrote:

> 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
> 
> -- 
> aprilshowers
0
Utf
12/21/2009 5:43:01 PM
On Mon, 21 Dec 2009 09:21:01 -0800, april <april@discussions.microsoft.com>
wrote:

>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

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
0
Ron
12/21/2009 5:57:57 PM
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
-- 
aprilshowers


"Eduardo" wrote:

> Hi,
> 
> =TRIM(RIGHT(A1,FIND(" ",A1)+2))
> 
> if this helps please click yes thanks
> 
> "april" wrote:
> 
> > 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
> > 
> > -- 
> > aprilshowers
0
Utf
12/21/2009 6:55:01 PM
On Mon, 21 Dec 2009 10:55:01 -0800, april <april@discussions.microsoft.com>
wrote:

>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

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
0
Ron
12/22/2009 12:14:29 AM
Reply:

Similar Artilces:

what is the format for an input cell?
I have Office - Student and Teacher Edition 2003 I am having trouble making a table ... I try to make a Data Table and try to define $E$3:$G$16 as the range in the row input cell area so I can make this a one-input data table and then I enter C$9$ for the column cell input area a pop up pops up saying that the input (row) cell reference is invalid I hope this not a bug that can only be fixed if you have the full version of the software any clues? it should be $C$9 Pumaman <Pumaman@discussions.microsoft.com> wrote in message news:D68327C9-5237-4353-8474-890677696F9...

Average in Cells
I know how to calculate the Average within a range of cells - however, what I would like to do is calculate the average but for only those cells that return a figure based on the results of another formula - example:if you have 6 cells but only 3 cells return a value, I would like the average calculated on the 3 cells and not based on 6 - and similarly, if another 2 figures are entered, the formula then calculates the average of the 5 figures - hope this makes sense. -- martins ------------------------------------------------------------------------ martins's Profile: http://www.excel...

Macro to clean empty cells
As I have had some great help from this group before, here is another request. Am looking to clean up some sheets in various workbooks in Excel 2007 Anybody able to help me out with a macro that will look for empty cells and then clear them out of all formats or hidden characters etc. that they may have but cant be seen. Will only need to run it on individual named sheets rather than on whole workbook many thanks ...

add multiple entries to contents of worksheet
HI all, is there a quicker way to add multiple entries to the contents of a worksheet. I need to add all suppliers to a 206 worksheet for tax change reasons. Thanks in advance. Assuming the tax has changed for those supplier entries, right-click on the Contents tab and select Recently Changed. The default will be today's changes. This trick works for most worksheets. If you want to send down various table updates on a daily basis, select the manual filter option for the worksheet, then right-click on Contents tab to add all recently changed entries to that worksheet. Click Appro...

copy cells but lock editing
Hi everyone I have a spreadsheet where I have locked cells and password protected. within this sheet though I have not locked a specific range of cells as they are used in another sheet as source data. Is there a way that I can allow these cells to still be copied for the source data but not actually changed by other users?? -- Thanks as always Lise If you lock the cells and protect the worksheet, what happens when the user tries to copy those cells? Lise wrote: > > Hi everyone > > I have a spreadsheet where I have locked cells and password protected. ...

Display one number in a cell even though 2 numbers get pasted into the same cell?
I am trying to figure out how to format a cell so that it only display the first number, even though two numbers are posted in the cell. The reason that two numbers are posted in the cell is that I am copyin the data from a website and when I post it into excel, it is using HTM format to paste... For example, if I post a column, it will have numbers set up lik this: +3 -109 -3 -103 +11 -110 -10 -105 The +3 -109 would all be in the same cell. I need each one of thos cells to just display +3, then -3, then +11, then -10 after I past them into the worksheet. I want to take just the first...

Cell Values #2
How, by using VB can you differentiate between a blank cell and a cel with the value of zero? Thanks -- jtrevil ----------------------------------------------------------------------- jtrevill's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1660 View this thread: http://www.excelforum.com/showthread.php?threadid=31503 See possible answer in your other thread -- Regards Ron de Bruin http://www.rondebruin.nl "jtrevill" <jtrevill.1fxhpz@excelforum-nospam.com> wrote in message news:jtrevill.1fxhpz@excelforum-nospam.com... > > How, by usi...

extracting data from one cell to another
I have a sheet that i downloaded from our erp system that has one column with text. the text is long and descriptive. the only thing is the same is that there is one piece that starts with QN then a blank then 11 numbers like this. QN 40000152489. What i want to do is pull that from the text cell to another cell. Is that possible? Try =MID(A2,FIND("QN ",A2),14) and copy down -- HTH Bob "pat67" <pbuscio@comcast.net> wrote in message news:e44c09be-083e-4c7b-ae63-49f0f7ac153a@x5g2000vbf.googlegroups.com... >I have a sheet that i downloaded ...

Hand cursor (with mailto: info) appears over other cells.
I have a user who uses the hyperlink feature in Excel to open up new messages to clients by clicking on their e-mail addresses within the spreadsheet. We've recently seen a behavior where even if she clicks over the client's name or address, the hand cursor still appears and instead of being able to edit the cell, it opens a new email to that client. I'm not sure what has happened. Any ideas? ...

formula for counting charachters in a cell ?
could anyone tell me the formula for counting charachters in a cell In article <E319A433-C6D8-4772-9450-68805070BC58@microsoft.com>, "Leezo" <Leezo@discussions.microsoft.com> wrote: > could anyone tell me the formula for counting charachters in a cell Try... =LEN(A1) Hope this helps! =LEN("cell") "Leezo" wrote: > could anyone tell me the formula for counting charachters in a cell Markos Mellos Wrote: > =LEN("cell") > > "Leezo" wrote: > - > could anyone tell me the formula for counting charachters ...

Mouse continues to select after clicking on a cell in excel.
I have read numerous posts detailing this issue and all the replies have been unhelpful. So hopefully, I can get some actual help for this issue. This issue seems to happen randomly, and effects one out of 10 similar machines with the same setup. All the machines are running windows 2000 (fully patched to the latest service/security level), running Office XP, also fully patched to the latest service/securit level. The problem is, a cell gets selected and the mouse continues to select cells even thou the mouse button is not pressed, basically disabling excel. The only way to stop thi...

Data from web query to a single cell
Hi All, How do I make data coming from a web query to populate a single cell . The data from http://finance.yahoo.com/d/quotes.csv?s=vclk&f=sl1 occupies two cells one below the other. Any help would be greatly appreciated. Thanks Kash Strange. xphome xl97sr2 put symbol, value in same cell xl2002 put symbol in col A and value in Col B. -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "khosa" <khosa19@yahoo.com> wrote in message news:8ac3d73b.0307311518.1d8efb1c@posting.google.com... > Hi All, > > How do I make data coming from a web query t...

Pause and Input to cell
Help, Back in a Lotus 1-2-3 macro we could use the command {?} to pause for input, then the macro would place the input data in a cell, let's say d9. How do you do this in Excel. Val, InputBox. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Val Steed" <vals@msn.com> wrote in message news:uEGWBOXTEHA.1168@TK2MSFTNGP11.phx.gbl... > Help, > > Back in a Lotus 1-2-3 macro we could use the command {?} to pause for input, > then the macro would place the input data in a cell, let's say d9. > >...

How do I return the cell address of the largest of a set of values
I have a table of integers ranging from cells B3 to Z51, and I want to find the cell address of the largest value. =LARGE(B3:Z51, 1) will give me the largest value, but how do I find the cell address where that largest value is located? Hello Try this array formula (validate with Ctrl + Shift + Enter): =ADDRESS(MIN(IF(B3:Z51=LARGE(B3:Z51,1),ROW(B3:Z51))),MIN(IF(B3:Z51=LARGE(B3:Z51,1),COLUMN(B3:Z51)))) HTH Cordially Pascal "Mr. Snrub" <Mr. Snrub@discussions.microsoft.com> a �crit dans le message de news: 5ECBDE62-5667-4376-9CCA-A8CE9E40F210@microsoft.com... >I have a ...

Comparing contents of 2 sheets
Hi, I have 2 sheets, one for 2002 and one for 2003. They contain products and quantities sold and I want to be able to compare how my sales are doing on sheet 3. In 2002 I sold 50 different types of products but in 2003 i have expanded my range to over 100, so I cannot just do a compare of them on sheet 3. I heard that you can use pivot tables etc. but how? Can someone please explain basically what i have to do. Heres a small sample 2002 Product qty Shoes 10 bag 4 jumpers 6 2003 Product ...

different formatting, same cell
Say the date that a certain event occurs (2/17/07) is in cell A1. I would like to have cell B1 display: Completed 2/17/07 If I use the formula ="Completed "&A1, it displays: Completed 39130 Is there another way to do this? To Excel, dates are just numbers, so you need to tell it to convert the number (date) to text how to display that text. Try something like this: ="Completed "&TEXT(A1,"MM/DD/YYYY") Does that help? *********** Regards, Ron XL2002, WinXP "Matt" wrote: > Say the date that a certain event occurs (2/17/07) is in cell A1....

auto expand cells/rows?
Is there a way to force the row to expand as a my coworkers type in text data to a cell? Mike, If by expand, you mean have the row height automatically increase, use Format - Rows - Autofit. If you manually adjust the row height, it turns off Autofit. With Autofit on, changing the size (font) will cause the row height to adjust automatically to accomodate. If you have Wrap Text turned on (for a multiline cell within the cell's width) (Format - Cells - Alignment) the row height will adjust as you add/remove text from the cell. -- Earl Kiosterud mvpearl omitthisword at verizon peri...

Convert data into one WrapText cell
User's data is as follows: G Data......................... 4 no 5 20 more Data ............... etc where G through 20 is in a single row, and therefore Data occupies 3 rows. I believe this gives me a complete record where the Data occupies more than one row: Sub TestConvertToWrapText() Dim Cell As Range, StartRw As Long Dim EndRw As Long For Each Cell In ActiveSheet.Range("B2:B33") If Not IsEmpty(Cell) Then StartRw = Cell.Row If Not IsEmpty(Cell.Offset(1, 0)) Then EndRw = Cell.End(xlDown).Row...

Reading a COMPLEX CONTENT : Stan Can you help?
Hi STAN, Stan: Thanks for your response to my previous post on reading a XSD file using your article in "https://blogs.msdn.com/stan_kitsis/archive/2005/08/06/448572.aspx". it works quite well but I have one problem.. I am not able to read a Complex Content.. Here is a portion of the XSD that contains the complex content. I need to read the elements under it and could not get an handle to it.. Could you please help? Thanks, Ganesh *********************** <xs:complexType name="UserBasic"> <xs:complexContent mixed="false"> <xs:exte...

Cells shows current month only in number format
Hello. How can make a cell show the current monht? So for if its October it shows the number 10 in a cell? I've tried data, today, etc... the closest I've gotten is to show an O for October, but I need the number. Thanks =CHOOSE(MONTH(TODAY()),1,2,3,4,5,6,7,8,9,10,11,12) -- hope to help, cm "Benny" wrote: > Hello. How can make a cell show the current monht? So for if its October it > shows the number 10 in a cell? > > I've tried data, today, etc... the closest I've gotten is to show an O for > October, but I need the number. Thanks act...

Split name cell
I have a spreadsheet with one column that contains a list of names in the following format: LASTNAME, Firstname Is it possible to automatically split this into two columns, one for lastname, and one for firstname, using the comma as the basis of where to make the split? -- Aaron Stamboulieh - MCSA, A+ Aaron, Select your range containing the names MAKE SURE you have an empty column to the right From the menu "Data" -> "Text to Columns" Choose delimited and choose comma as the delimiter Dan E "Aaron Stamboulieh" <stamfamremovethis@videotron.ca> wr...

Format Cell Problem
When clicking on the format cell option on one of our computers, it immediately closes Excel out with an error stating that excel.exe has generated errors and had to be closed. It doesn't matter if you do it from the top menu or if you right click on the mouse. We have tried the repair option as well as uninstalling and reinstalling excel. Nothing has worked so far. Any suggestions on how to solve this problem. Thanks. Try this: 1. open a fresh, clean workbook 2. select all the cells 3. Edit > Clear > Formats Then try to set a format -- Gary''s Student - gsnu2007...

shading every other cell
I've created a large reference sheet, and would like every other cel shaded to make it easier to use. Usually I leave the first row white shade the second row, then copy both and paste to the rest of the shee to get the every other cell effect. However, there are a few cells i my worksheet that are larger than the rest, and when I hit past special - formats, it changes the size of those cells in addition t the shading. Is there a simpler way to shade every other cell, or past some of the formatting without pasting all of it? Thanks, Stac ----------------------------------------------- ~~...

select certain cells
I want to select cells A1:A5:A9:A13:A17:A21 etc. etc. A4021: A4025 So, I want to select A1 + 4. I would be very thankful if someone could help me. Ragards, Melle -- melle ------------------------------------------------------------------------ melle's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26981 View this thread: http://www.excelforum.com/showthread.php?threadid=401913 a bit more detail might help -- Don Guillett SalesAid Software donaldb@281.com "melle" <melle.1uw02b_1125925504.0021@excelforum-nospam.com> wrote in message news:m...

Pasting without updating cell references in formulas
I am a relatively low end user of Excel. I need to copy a large section of formulas to create a new section of my spreadsheet. Excel does an excellent job of changing all cell reference in the formulas based on the number of cells that you cover in your copy and paste. HOWEVER, in this case I want almost all cell references to remain the same. I can then edit each one and change the one that I want changed. Is there any easy way to do this. I'm sure there is. Thanks in advance Reg As long as you are moving to the same sheet, just highlight the cells you want to move and using mou...