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]

If your data is in C17, then this will give you 123 etc.

=VALUE(MID(C17,2,99))

 

[jmccabestrak]

I believe I was a bit confusing. I want to be able to search "123" or "12345" and have them search my data for "S00000123" and "S00012345" respectively. Is there a way to search the end of the strings in my data?

 

[gaz_chops]

So C17 has the value e.g. 123 and column M has S00000123?
Are the codes always 9 characters?

 

[gaz_chops]

Try

=HYPERLINK(LOOKUP(9.99999999999999E+307,SEARCH(REPT("0",8-LEN(C17))&C17,UK!$D$1:$D$100),UK!$M$1:$M$100))

 

[jmccabestrak]

So C17 has the value e.g. 123 and column M has S00000123?
Are the codes always 9 characters?

Yes. all of my values are 9 characters starting with an S. However, Row D has the 9 digit code and row M has the Hyperlink address.

@Gaz_Chops I tried that but it came as a #Ref error. I read through and it does not include an S. I am not sure if that would cause the problem.

 

[gaz_chops]

It shouldn't, it searches for e.g. 123 but adds the leading zeros 00000123, otherwise it would find 123 in S00012345.

Seems to work for me.

[TABLE="width: 346"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[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/blue"]blue[/URL][/TD]
[TD="align: right"]75123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jmccabestrak]

It shouldn't, it searches for e.g. 123 but adds the leading zeros 00000123, otherwise it would find 123 in S00012345.

Seems to work for me.

[TABLE="width: 346"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[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/blue"]blue[/URL][/TD]
[TD="align: right"]75123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What is the significant of the 9.99999999999999E+307 in your code? Why is that the lookup value as opposed to SEARCH(REPT("0",8-LEN(C17))&C17


EDIT I have also gotten it to work, thank you. However still would like to know the significance just for future knowledge

 

[gaz_chops]

It enables the search to be used, read THIS for an explanation from Aladin.

You can also use, both should work.

=HYPERLINK(LOOKUP(2,1/(SEARCH(REPT("0",8-LEN(C17))&C17,$D$8:$D$27)),$M$8:$M$27))

 

[jmccabestrak]

It enables the search to be used, read THIS for an explanation from Aladin.

You can also use, both should work.

=HYPERLINK(LOOKUP(2,1/(SEARCH(REPT("0",8-LEN(C17))&C17,$D$8:$D$27)),$M$8:$M$27))

I have gotten it to successfully work searching for 123. is there anyway to still allow the S00000123 search to wokr alongside with the shortened search. I'm looking to grant my users the most possible options.