extract names from string and list the name

[scamsel]

Hi, so i'm trying to be able to put a formula in a cell that will return the last name of the person listed in a string, where the name is not in the same position each time. I know there are some complicated formulas to achieve this but i was trying to think of a way to make the formula a little more simple by maybe referring to a small list of names to look within a table.

So for example:

RACETRAC2502 025023HOLLY SPRINGS MICHAEL HOLSENBECK-01196-071789294 770-4317600

SHELL OIL 5754237640KENNESAW GA DAVID HALL-02004-0083527 GAS STATION

SHELL OIL 5754237640KENNESAW GA DAVID HALL-02004-0083451 AUTO FUEL DISPENSER

QT MARIETTA GA JOHN TURSCHMANN-01014-00743048SYB OUTSIDE SALE

QT MARIETTA GA GELTON FLORES-01162-0074304UDJE OUTSIDE SALE

QT MARIETTA GA KRISTOPHER BAXSTER-02103-0074301D8QK INSIDE SALE

for the list above, i would like to be able to search each string and pull out the last name present in each. so for example, first cell would return me 'Holsenbeck', second and third would return 'Hall', fourth cell 'Turschmann', so instead of having some huge nested formula with each name to look up, is there a way to reference a list that i maintain anytime new names are added?

thank you,
b

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D
1
2	RACETRAC2502 025023HOLLY SPRINGS MICHAEL HOLSENBECK-01196-071789294 770-4317600	Holsenbeck		Hall
3	SHELL OIL 5754237640KENNESAW GA DAVID HALL-02004-0083527 GAS STATION	Hall		Flores
4	SHELL OIL 5754237640KENNESAW GA DAVID HALL-02004-0083451 AUTO FUEL DISPENSER	Hall		Baxster
5	QT MARIETTA GA JOHN TURSCHMANN-01014-00743048SYB OUTSIDE SALE	Turschmann		Turschmann
6	QT MARIETTA GA GELTON FLORES-01162-0074304UDJE OUTSIDE SALE	Flores		Holsenbeck
7	QT MARIETTA GA KRISTOPHER BAXSTER-02103-0074301D8QK INSIDE SALE	Baxster
8
Main

Cell Formulas
Range	Formula
B2:B7	B2	=LOOKUP(2,1/(ISNUMBER(SEARCH($D$2:$D$6,A2))),$D$2:$D$6)

 

[Peter_SSs]

In case there could be a situation where one name is a subset of another, as in my example below, I would suggest a modification - see column C

20 12 12.xlsm
	A	B	C	D
1
2	RACETRAC2502 025023HOLLY SPRINGS MICHAEL HOLSENBECK-01196-071789294 770-4317600	Holsenbeck	Holsenbeck	Shaller
3	SHELL OIL 5754237640KENNESAW GA DAVID HALL-02004-0083527 GAS STATION	Hall	Hall	Flores
4	SHELL OIL 5754237640KENNESAW GA DAVID SHALLER-02004-0083451 AUTO FUEL DISPENSER	Hall	Shaller	Baxster
5	QT MARIETTA GA JOHN TURSCHMANN-01014-00743048SYB OUTSIDE SALE	Turschmann	Turschmann	Turschmann
6	QT MARIETTA GA GELTON FLORES-01162-0074304UDJE OUTSIDE SALE	Flores	Flores	Holsenbeck
7	QT MARIETTA GA KRISTOPHER BAXSTER-02103-0074301D8QK INSIDE SALE	Baxster	Baxster	Hall
Extract Name

Cell Formulas
Range	Formula
B2:B7	B2	=LOOKUP(2,1/(ISNUMBER(SEARCH($D$2:$D$7,A2))),$D$2:$D$7)
C2:C7	C2	=LOOKUP(9^9,SEARCH(" "&D$2:D$7&"-",A2),D$2:D$7)

 

[scamsel]

wow this is great, appreciate it yall.

question, would either of you mind help explaining what this formula actually is doing?

=LOOKUP(2,1/(ISNUMBER(SEARCH($D$2:$D$6,A2))),$D$2:$D$6)

 

[Fluff]

This part

ISNUMBER(SEARCH($D$2:$D$6,A2))

will return an array of true/false depending on whether a name in D2:D6 is found in A2
{FALSE;FALSE;FALSE;FALSE;TRUE}
when 1 is divided by that array you get
{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1}
The lookup functions then looks for a value that <=2 and returns the relevant value from D2:D6

 

[scamsel]

ha, wow so great, got it thanks fluff!

 

[Fluff]

Glad we could help & thanks for the feedback.