vba excel -- having issues with INSTR and Like functions

[terriertrip]

I have some strings:

Please Login or Register  to view this content.

I have a datasheet from an outside source that outputs data with the attribute names within the numeric data itself (smh).On top of this, the data headers are not even consistent, and some sources have 1 range of data and others have multiple columns, so I've been tasked on munging and compiling all these disparate sources into one table. Luckily they're all csv files. I remove the alpha characters once I've identified the address of the data range, but I've been trying to use Instr and Like to locate the first instance using the predefined sAttribute() values. Once I have the address, I can then remove the string variables from the numeric data easily and got that working fine; however, I can't get my initial procedure to match the partial string. For example, a data source comes with a 'ppb' suffix attached to all the data and I want to look the first instance of sAttribute(1). I've tried using wildcards and it still doesn't work.

Please Login or Register  to view this content.

I've also tried using with no success:

Please Login or Register  to view this content.

[Norie]

When using Like the wildcards should be applied to the string you are searching for.

So if you wanted to find if A1 contains 'pbb' you would use something like this.

Please Login or Register  to view this content.

[terriertrip]

Ok that makes sense. So what if Range("A1").value is 'pbb' and the search criteria (after the Like) has a 'tvoc_' in front of the 'pbb', how do I rectify those initial characters within the search criteria? Or say that a value in A1 has a string of characters before and after what is being searched. For example if the value in A1 is -465.3 ppbAc* and my search criteria is 'tvoc_ppb'. Is this possible without having to do some manual formatting?

[Norie]

If the search criteria is 'tvoc_ppb' then surely '-465.3 ppbAc*' isn't a match?

[terriertrip]

Hello Norie. I guess I'm not wording it correctly, I'm looking for only a partial match. That being, any value that has the letters 'pbb' in it, in that order. The value could have other superfluous characters in it.

I guess I could change the search criteria to just 'ppb' and then include another condition later down the line that would change range header to 'tvoc_ppb'. But I'm more curious if there is a way to find a match, partial or otherwise, of a string using only a partial string as the criteria, or does the criteria need to be absolute?

[rorya]

How is the code supposed to know that it should try and match just the ppb part of the tvoc_ppb search criterion?