How to extract names with variable lenths from text strings using Excel?

[Molar]

I need to extract names from a text string but they are variable in length.
See example below:

PETER PAN PO LLL555555D AUB-1111111 2/27/10 1079.65 1012348
SUPERHERO MAN GGG444444C AUB-0000000 8/02/10 108.00 704546
SUPERHERO WOMAN PO GSS011111F AUB-1111111 3/13/10 11.26 1028549

The names I am trying to extract are:
PETER PAN
SUPERHERO MAN
SUPERHERO WOMAN

How would you do this?

 

[petertenthije]

The below formula will only work when the customer name does NOT container numbers.

=IF(LEFT(RIGHT(LEFT($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-4),4),1)=" ",LEFT($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-4-4),LEFT($A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-4-1))


To break the formula down, this formula does this:

• It identifies the position of the first number in the cell

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A14&"0123456789"))
​

• It looks up the value four cells to the left of the first number.

=LEFT(RIGHT(LEFT($A1,$C1-4),4),1)​

• If fourth cell before the first number is a space, then the two letter code (PO) is applicable, otherwise the assumption is no two letter code.

=IF(STEP2=" ",LEFT($A1,STEP1-4),LEFT($A1,STEP1-1))​

Two possible issues:

• A customer name with a number in it will give bad results
• A customer name that ends with a two letter string will give bad results.

 

[Molar]

Yes I did but it didn't work as well as the prior one. There was data that was left off.

 

[Molar]

The formula stated in post #11 worked perfect. Thank you!!