Identify text within a string

[miyo]

Hi,

I have a cell for a T&E report that says "purchased by Smith Delta flight". I want to match the last name to a list of employees last names, ex. "Jones, Smith, Jefferson." I know I can use a find function to identify if a specific last name is in the text string. Is there a way in one formula to look at the three last names and return which one is in the text string (so I know who to charge for the flight w/o doing a nasty manual exercise)?

Thanks

 

[Jeff Meyers]

Here's an approach to check for three last names. It would work for a few more last names, but you would need a different approach for many names.

Excel Workbook
	A	B	C
1	Last Name	Expense Description	Charge To
2	Jones	purchased by Smith Delta flight	Smith
3	Smith
4	Jefferson
Sheet2

Excel 2010

Cell Formulas
Range	Formula
C2	=IF(ISNUMBER(FIND(A2,B2)),A2,IF(ISNUMBER(FIND(A3,B2)),A3,IF(ISNUMBER(FIND(A4,B2)),A4,"Not found")))

 

[miyo]

Return text from a column where all other rows are blank

I have 48 names. I'll just put them in separate columns, not sure there's another way. Now that I have them in columns is there a way to look at row 4 columns C-AX and return the name that is in one column (all other columns are blank since the last names don't match)?

 

[Jeff Meyers]

#VALUE!

Excel 2010

Cell Formulas
Range	Formula
B5	=INDEX($C$3:$AX$3,MAX(C5:AX5)-2)
B6	=INDEX($C$3:$AX$3,MAX(C6:AX6)-2)
B7	=INDEX($C$3:$AX$3,MAX(C7:AX7)-2)
B8	=INDEX($C$3:$AX$3,MAX(C8:AX8)-2)
B9	=INDEX($C$3:$AX$3,MAX(C9:AX9)-2)
B10	=INDEX($C$3:$AX$3,MAX(C10:AX10)-2)
B11	=INDEX($C$3:$AX$3,MAX(C11:AX11)-2)
C5	=IF(ISNUMBER(FIND(C$3,$A5)),COLUMN(),"")
C6	=IF(ISNUMBER(FIND(C$3,$A6)),COLUMN(),"")
C7	=IF(ISNUMBER(FIND(C$3,$A7)),COLUMN(),"")
C8	=IF(ISNUMBER(FIND(C$3,$A8)),COLUMN(),"")
C9	=IF(ISNUMBER(FIND(C$3,$A9)),COLUMN(),"")
C10	=IF(ISNUMBER(FIND(C$3,$A10)),COLUMN(),"")
C11	=IF(ISNUMBER(FIND(C$3,$A11)),COLUMN(),"")
D5	=IF(ISNUMBER(FIND(D$3,$A5)),COLUMN(),"")
D6	=IF(ISNUMBER(FIND(D$3,$A6)),COLUMN(),"")
D7	=IF(ISNUMBER(FIND(D$3,$A7)),COLUMN(),"")
D8	=IF(ISNUMBER(FIND(D$3,$A8)),COLUMN(),"")
D9	=IF(ISNUMBER(FIND(D$3,$A9)),COLUMN(),"")
D10	=IF(ISNUMBER(FIND(D$3,$A10)),COLUMN(),"")
D11	=IF(ISNUMBER(FIND(D$3,$A11)),COLUMN(),"")
E5	=IF(ISNUMBER(FIND(E$3,$A5)),COLUMN(),"")
E6	=IF(ISNUMBER(FIND(E$3,$A6)),COLUMN(),"")
E7	=IF(ISNUMBER(FIND(E$3,$A7)),COLUMN(),"")
E8	=IF(ISNUMBER(FIND(E$3,$A8)),COLUMN(),"")
E9	=IF(ISNUMBER(FIND(E$3,$A9)),COLUMN(),"")
E10	=IF(ISNUMBER(FIND(E$3,$A10)),COLUMN(),"")
E11	=IF(ISNUMBER(FIND(E$3,$A11)),COLUMN(),"")
F5	=IF(ISNUMBER(FIND(F$3,$A5)),COLUMN(),"")
F6	=IF(ISNUMBER(FIND(F$3,$A6)),COLUMN(),"")
F7	=IF(ISNUMBER(FIND(F$3,$A7)),COLUMN(),"")
F8	=IF(ISNUMBER(FIND(F$3,$A8)),COLUMN(),"")
F9	=IF(ISNUMBER(FIND(F$3,$A9)),COLUMN(),"")
F10	=IF(ISNUMBER(FIND(F$3,$A10)),COLUMN(),"")
F11	=IF(ISNUMBER(FIND(F$3,$A11)),COLUMN(),"")
G5	=IF(ISNUMBER(FIND(G$3,$A5)),COLUMN(),"")
G6	=IF(ISNUMBER(FIND(G$3,$A6)),COLUMN(),"")
G7	=IF(ISNUMBER(FIND(G$3,$A7)),COLUMN(),"")
G8	=IF(ISNUMBER(FIND(G$3,$A8)),COLUMN(),"")
G9	=IF(ISNUMBER(FIND(G$3,$A9)),COLUMN(),"")
G10	=IF(ISNUMBER(FIND(G$3,$A10)),COLUMN(),"")
G11	=IF(ISNUMBER(FIND(G$3,$A11)),COLUMN(),"")
AW5	=IF(ISNUMBER(FIND(AW$3,$A5)),COLUMN(),"")
AW6	=IF(ISNUMBER(FIND(AW$3,$A6)),COLUMN(),"")
AW7	=IF(ISNUMBER(FIND(AW$3,$A7)),COLUMN(),"")
AW8	=IF(ISNUMBER(FIND(AW$3,$A8)),COLUMN(),"")
AW9	=IF(ISNUMBER(FIND(AW$3,$A9)),COLUMN(),"")
AW10	=IF(ISNUMBER(FIND(AW$3,$A10)),COLUMN(),"")
AW11	=IF(ISNUMBER(FIND(AW$3,$A11)),COLUMN(),"")
AX5	=IF(ISNUMBER(FIND(AX$3,$A5)),COLUMN(),"")
AX6	=IF(ISNUMBER(FIND(AX$3,$A6)),COLUMN(),"")
AX7	=IF(ISNUMBER(FIND(AX$3,$A7)),COLUMN(),"")
AX8	=IF(ISNUMBER(FIND(AX$3,$A8)),COLUMN(),"")
AX9	=IF(ISNUMBER(FIND(AX$3,$A9)),COLUMN(),"")
AX10	=IF(ISNUMBER(FIND(AX$3,$A10)),COLUMN(),"")
AX11	=IF(ISNUMBER(FIND(AX$3,$A11)),COLUMN(),"")

 

[tigeravatar]

miyo,

Assuming a setup similar to this:
<TABLE style="WIDTH: 389pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=519><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 153pt; mso-width-source: userset; mso-width-alt: 7460" width=204><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=64>

​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 153pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=204>

A​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=75>

B​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>

C​

</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 84pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=112>

D​

</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=21 align=right>

1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Expense Description</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Charge To</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Last Names List</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 align=right>

2​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Purchased by Smith Delta flight</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Smith</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Jones</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 align=right>

3​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Smith</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 align=right>

4​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Jefferson</TD></TR></TBODY></TABLE>

The formula in B2 is:
=INDEX($D$2:$D$4,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(" "&$D$2:$D$4&" ",A2)),),0))

Column D can hold as many names as you want, adjust the D2:D4 range to suit.

This assumes the last name always has a space before and after it in the Expense Description as shown here

 

[miyo]

The first post definitely works which is great. I'm trying to get the second post to work since it's should be much easier, but having trouble since it varies whether there are spaces before and after the name in my data, great tip though!