Finding text within a text string and a lot lot more!

[aj2k2001]

This is my first post so i do appologise if this question has already been answered - but i am well and truly stuck.

A B C
<TABLE style="WIDTH: 348pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=464 border=0><COLGROUP><COL style="WIDTH: 200pt; mso-width-source: userset; mso-width-alt: 9728" width=266><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 200pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=266 height=20>AcctNum</TD><TD class=xl70 id=td_post_1782397 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=79>Desk</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 89pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=119>Instrument</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20><TABLE style="WIDTH: 200pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=266 border=0><COLGROUP><COL style="WIDTH: 200pt; mso-width-source: userset; mso-width-alt: 9728" width=266><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 id=td_post_1782397 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 200pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=266 height=20>LN_AF_NY_CCY_CONV</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>LN_AF_NY_CCY_CONV</TD></TR></TBODY></TABLE></TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"><TABLE style="WIDTH: 59pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=79 border=0><COLGROUP><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 id=td_post_1782397 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=79 height=20>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>#N/A</TD></TR></TBODY></TABLE></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"><TABLE style="WIDTH: 89pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=119 border=0><COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4352" width=119><TBODY><TR style="HEIGHT: 15pt" height=20><TD id=td_post_1782397 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 89pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=119 height=20>CCY_CONV</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>CCY_CONV</TD></TR></TBODY></TABLE></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD class=xl69 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>Above is an extract from the spreadsheet that is over 400,000 rows.

Ok, basically I need to search for the Desk name within the AcctNum and then post the Desk name in column B. NB There are around 40 different desk names (this can be stored as a list). So for example, within this AcctNum the desk name is AF_NY. However, there are AcctNum's which do not have the Desk name within them and would therefore (i assume) return an error when searching within them? It would be useful if it could return #N/A in order to conform with other postings within column B.

Along a similar line i need to search for the Instrument within the AcctNum. So for this example I would need to find CCY_CONV and then post CONV in column C. There are around 9 different instruments and these can also be stored in a separate list.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks for your help and good luck!<o></o>

 

[Jonmo1]

Try this array formula

=INDEX($E$2:$E$6,MATCH(2,1/ISNUMBER(SEARCH($E$2:$E$6,A2))))

IMPORTANT!!!!!
This is an array formula that requires CTRL + SHIFT + ENTER
After entering the formula IN ONE CELL, highlight that cell and press F2
Then Press CTRL + SHIFT + ENTER
When Entered correctly, the formula will be enclosed in {brackets}
Then you can Fill the formula down...

Hope that helps..

Personal.xls
	A	B	C	D	E
1	AcctNum		DeskName		ListofDeskNames
2	LN_AF_WS_CCY_CONV		AF_WS		AF_CO
3	LN_AF_CO_CCY_CONV		AF_CO		AF_CA
4	LN_AF_CA_CCY_CONV		AF_CA		AF_NY
5	LN_AF_NY_CCY_CONV		AF_NY		AF_MN
6	LN_AF_MN_CCY_CONV		AF_MN		AF_WS
7	LN_CCY_CONV		#N/A
Sheet1

 

[Ron Coderre]

(This works in XL2003...not for 400,000 rows, of course)
Using your posted structure
AND
with F1:F40 containing the list of Desk names (no blank cells)

Then...
with A1: (an AcctNum)

This regular formula returns the Desk embedded in that AcctNum (if any).
Otherwise, it returns #N/A

B1: =LOOKUP(999,SEARCH($F$1:$F$40,A1),$F$1:$F$40)

Note: If the AcctNum contains more than one Desk name,
the last one to the right will be returned.

Is that something you can work with?