# Searching for part of a text string

#### jmccabestrak

##### New Member
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.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
If your data is in C17, then this will give you 123 etc.

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

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?

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

Try

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.

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.

Code:
``````[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]``````

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.

Code:
``````[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

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

You can also use, both should work.

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

You can also use, both should work.

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.

Replies
10
Views
1K
Replies
1
Views
882
Replies
13
Views
2K
Replies
14
Views
671
Replies
0
Views
1K

1,196,101
Messages
6,013,459
Members
441,767
Latest member
Craigh4444

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back