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?

 

[jtakw]

Hi,

Can it be possible that the names are more than 2 parts (e.g. First Middle Last, First M. Last) and/or have Titles (e.g. Mr. Mrs. Miss)?

 

[Molar]

It could be names reflected as last, first, middle initial or business names and they can contain / or , .

 

[jtakw]

Please show more samples containing all different possibilities, and what you want extracted.

If there's no "pattern" or "uniformity" to the data, I won't be able to help, the following is based ONLY on your OP:

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

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",2))-1)

 

[Molar]

The formula worked for those names where there were only two parts to the name. However, there are names were there are up to four or five parts. And no there is no pattern or uniformity to the data. See below example.

CORPORATION EAST LOG BASE GGG333333A AAA-0980988 11/20/20 105.59 769344
GYIDR FEELING SECURITY PO GGG200800K AAA-0980989 11/17/20 .26 784044
MARTION MR HELLO KITTY GGG000705D AAA-0980999 11/20/25 225.24 1207213

 

[István Hirsch]

Then how could you characterize the substring following the names (GGG333333A etc)?

 

[petertenthije]

If I get this straight, behind the name you will always get either:

• PO plus a reference number consisting of ***######*
• a reference number consisting of ***######*
• The names themselves do not contain numbers

If above assumption is correct, you can use the following formula:
=LEFT(A4,IFERROR(FIND(" PO ",A4,1)-1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A4&"0123456789"))-4))


edited: changed from Dutch Excel formatting to English (commas instead of semicolons)

 

[István Hirsch]

If the structure behind the name is the same, try this:

Excel Workbook
	A	B
1	CORPORATION EAST LOG BASE GGG333333A AAA-0980988 11/20/20 105.59 769344	CORPORATION EAST LOG BASE
2	GYIDR FEELING SECURITY PO GGG200800K AAA-0980989 11/17/20 .26 784044	GYIDR FEELING SECURITY PO
3	MARTION MR HELLO KITTY GGG000705D AAA-0980999 11/20/25 225.24 1207213	MARTION MR HELLO KITTY
4	PETER PAN PO LLL555555D AUB-1111111 2/27/10 1079.65 1012348	PETER PAN PO
5	SUPERHERO MAN GGG444444C AUB-0000000 8/02/10 108.00 704546	SUPERHERO MAN
6	SUPERHERO WOMAN PO GSS011111F AUB-1111111 3/13/10 11.26 1028549	SUPERHERO WOMAN PO
Sheet1

 

[Molar]

Let me clarify the data that I need to extract per the example previously provided:

CORPORATION EAST LOG BASE GGG333333A AAA-0980988 11/20/20 105.59 769344
GYIDR FEELING SECURITY PO GGG200800K AAA-0980989 11/17/20 .26 784044
MARTION MR HELLO KITTY GGG000705D AAA-0980999 11/20/25 225.24 1207213

Data needed is:
CORPORATION EAST LOG BASE
GYIDR FEELING SECURITY
MARTION MR HELLO KITTY

As far as the assumptions:

• PO plus a reference number consisting of ***######*
  • The PO is not consistent. This is a two letter field that can vary or it will be blank
• a reference number consisting of ***######*
  • This is correct. This field will always contain 3 letters followed by 6 numbers and ends with 1 one letter.
• The names themselves do not contain numbers
  • For the most part names will not contain numbers.

The formula provided worked in most circumstances except where the "PO" were different letters.

 

[István Hirsch]

Have you seen the formula in post#8?