Pulling specific text string from a cell

Hello all,

I am working in a 9000 line spreadsheet where one of the cells contains name information that I need to put into a separate column

Few examples:


AllSource Smith, Peter 01/01/12
Flexible Staffing Luman, Thomas 02/05/12
OnBoard Services Wilmoth, Nicole01/29/12
Robert Half InternationalTsang, Dexter 05/06/12
At-Tech Vega, Joel 04/15/12

As you can see, most of the cells are different in some regard making it difficult to use text to columns or replacing. The thing that all of the cells in the column share is a "Lastname, Firstname" somewhere in the cell. I was wondering how I can just pull the name from these cells, such as:

Smith, Peter
Luman,Thomas
Wilmoth, Nicole
Tsang, Dexter
Vega, Joel

Thank you all, and please go easy! This is my first post.

Scott

Also what the forum deleted upon posting was large gaps inbetween some of the words. In the spreadsheet there are 17 blank spaces between Tech and Vega.

There are others much more skilled in advanced parsing techniques than I, but, did you intentionally show that in some cases there is no space between the title and last name, or no space between the first name and the date? Ex.
InternationalTsang
Nicole01/29/12

If there is no space, that would make it much harder. Also, is it only last name and first name? No middle names or initials, or double first names (e.g. Philips, Mary Jane)?

Pauley

Unfortunately that was not a mistake. Some of the names don't have spaces.

Also I am sure there are names with two first names etc..but for the vast majority of them it is the standard "Last, First" format. I don't mind if it doesn't work for some.

Assuming your input cells start at A1, in cell B1 use:
=TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",100)),100))

and in cell C1 use:
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-LEN(B1)-2)," ",REPT(" ",100)),100))

That seems to work for the three you provided, but will cause issues if there is no space between the name and the company. I'm sure there are better formulae, but these seem to work.

Pauley

Thanks Pauley, got it. Much appreciated.