Convert job numbers to text for wildcard search

[jaded62]

Hi all.

I am working on a scheduling application which allows the user to search for jobs (there's maybe 2000+ job numbers in the list) by number and name, dump the output to a listbox, from which the user selects the relevant job so they can schedule it.

My problem is the job numbers themselves. In their most basic format the are 4 digit numbers, being the first job we do on a particular site. From these jobs we then over time, do more jobs on the site and these are named as "sub jobs" using _Sxxxx or _Mxxxx. These sub job numbers are text and so are searchable with wildcards.

What I want to do is to convert the 4 digit job numbers to text for searching, then convert them back so they are unchyanged. If I were to do this manually in a sheet I'd insert a ' before each number so Excel regards it as text.

Is there an alternative to the ' preface method above that I can use in VBA?

Cheers.

 

[Farscape2020]

Instead of converting the 4 digit job numbers to text, using VBA, you could use the InStr function.

The general form is InStr([start], string1, string2, [compare]) where string1 would be the 4 digit code and string2 would the seach string. This function will also allow you to use wildcards so you can cover both options.