extract names from string and list the name

scamsel

New Member
Joined
Apr 2, 2007
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCD
1
2RACETRAC2502 025023HOLLY SPRINGS MICHAEL HOLSENBECK-01196-071789294 770-4317600HolsenbeckHall
3SHELL OIL 5754237640KENNESAW GA DAVID HALL-02004-0083527 GAS STATIONHallFlores
4SHELL OIL 5754237640KENNESAW GA DAVID HALL-02004-0083451 AUTO FUEL DISPENSERHallBaxster
5QT MARIETTA GA JOHN TURSCHMANN-01014-00743048SYB OUTSIDE SALETurschmannTurschmann
6QT MARIETTA GA GELTON FLORES-01162-0074304UDJE OUTSIDE SALEFloresHolsenbeck
7QT MARIETTA GA KRISTOPHER BAXSTER-02103-0074301D8QK INSIDE SALEBaxster
8
Main
Cell Formulas
RangeFormula
B2:B7B2=LOOKUP(2,1/(ISNUMBER(SEARCH($D$2:$D$6,A2))),$D$2:$D$6)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,540
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1
2RACETRAC2502 025023HOLLY SPRINGS MICHAEL HOLSENBECK-01196-071789294 770-4317600HolsenbeckHolsenbeckShaller
3SHELL OIL 5754237640KENNESAW GA DAVID HALL-02004-0083527 GAS STATIONHallHallFlores
4SHELL OIL 5754237640KENNESAW GA DAVID SHALLER-02004-0083451 AUTO FUEL DISPENSERHallShallerBaxster
5QT MARIETTA GA JOHN TURSCHMANN-01014-00743048SYB OUTSIDE SALETurschmannTurschmannTurschmann
6QT MARIETTA GA GELTON FLORES-01162-0074304UDJE OUTSIDE SALEFloresFloresHolsenbeck
7QT MARIETTA GA KRISTOPHER BAXSTER-02103-0074301D8QK INSIDE SALEBaxsterBaxsterHall
Extract Name
Cell Formulas
RangeFormula
B2:B7B2=LOOKUP(2,1/(ISNUMBER(SEARCH($D$2:$D$7,A2))),$D$2:$D$7)
C2:C7C2=LOOKUP(9^9,SEARCH(" "&D$2:D$7&"-",A2),D$2:D$7)
 

scamsel

New Member
Joined
Apr 2, 2007
Messages
31
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This part
Excel Formula:
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

New Member
Joined
Apr 2, 2007
Messages
31
Office Version
  1. 365
Platform
  1. Windows
ha, wow so great, got it thanks fluff!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,729
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,917
Messages
5,638,981
Members
417,061
Latest member
thematulaak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top