How can I extract upper case ticker symbols only?

[cfa_nyc]

My data contains cusips, names, and other info. I need to extract the ticker symbols in all caps (bolded to highlight) from each cell. Can anyone please assist?

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=384 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 288pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" width=384 colSpan=6 height=17> All Fund A A 62300Q711 DACAX 4/30/2007 05-0678718 0 4987</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=512 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD id=td_post_1974994 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 384pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" width=512 colSpan=8 height=17> Developed Markets Admin Administrative 652009672 DEALX 12/31/2001 01-0657718 0 88</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=8 height=17> Emerging Market All Asset A A 22250R231 EMAEF 6/30/2002 10-0452983 0 4019</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=8 height=17></TD></TR></TBODY></TABLE>

 

[jasonb75]

If your data is consistant, i.e. ticker is always immediately before the date then

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(A1,FIND("/",A1)-3))," ",REPT(" ",100)),100))

edit:-

in addition, if it is always 5 characters then you could just use

=TRIM(MID(A1,FIND("/",A1)-8,6))

Both methods are dependant on data consistancy.

 

[cfa_nyc]

Incredible! Thanks so much - it's very appreciated!

 

[jasonb75]

You're welcome, not sure if you noticed, but I did edit the reply with a simplified solution, the forum wouldn't show it as unread so could easily be missed.

 

[cfa_nyc]

I saw it & it's spot on as the data is always consitent.

thanks again!