Searching for part of a text string

[jmccabestrak]

I have created a database with data characterized in the format S12345678. If the number appears as S00000123, I want to be able to search "123" and pull up a hyperlink for it. Data can range from S00000110 to S80050000. I want to be able to search everything after the S while also eliminating all the unnecessary 0's after the S as well. (ie Search S00075000 as 75000 and S80012345 as 80012345. My current equation is =HYPERLINK(VLOOKUP($C$17,UK!D:M,10,FALSE)). I know I am a far way off but any help would be appreciated.

 

[gaz_chops]

Try

=HYPERLINK(IF(LEN(C17)=9,VLOOKUP(C17,UK!$D$8:$M$27,10,FALSE),LOOKUP(2,1/(SEARCH(REPT("0",8-LEN(C17))&C17,UK!$D$8:$D$27)),UK!$M$8:$M$27)))

[/FONT][TABLE="width: 346"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]S00000123[/TD]
[TD][/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S00075123[/TD]
[TD][/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S80012345[/TD]
[TD][/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]S00175000[/TD]
[TD][/TD]
[TD]pink[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][URL="http://www.mrexcel.com/forum/red"]red[/URL][/TD]
[TD]S00000123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][URL="http://www.mrexcel.com/forum/green"]green[/URL][/TD]
[TD]S80012345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][URL="http://www.mrexcel.com/forum/green"]green[/URL][/TD]
[TD="align: right"]80012345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][URL="http://www.mrexcel.com/forum/red"]red[/URL][/TD]
[TD="align: right"]123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[FONT=Lucida Grande]

 

[jmccabestrak]

Thank you for are all of your help. My search works perfectly!

 

[gaz_chops]

You're welcome, thanks for the feedback.