Check if a string contains another string

string vba
krishna picture krishna · Mar 23, 2013 · Viewed 854.8k times · Source

I want to find if a string contains a ","(comma) in it. Do we have any other option other than reading char-by-char?

Answer

rene picture rene · Mar 23, 2013

Use the Instr function

Dim pos As Integer

pos = InStr("find the comma, in the string", ",")

will return 15 in pos

If not found it will return 0

If you need to find the comma with an excel formula you can use the =FIND(",";A1) function.

Notice that if you want to use Instr to find the position of a string case-insensitive use the third parameter of Instr and give it the const vbTextCompare (or just 1 for die-hards).

Dim posOf_A As Integer

posOf_A = InStr(1, "find the comma, in the string", "A", vbTextCompare)

will give you a value of 14.

Note that you have to specify the start position in this case as stated in the specification I linked: The start argument is required if compare is specified.

Related questions

How Do I Convert an Integer to a String in Excel VBA?

How do I convert the integer value "45" into the string value "45" in Excel VBA?

Read More
How to extract file name from path?

How do I extract the filename myfile.pdf from C:\Documents\myfile.pdf in VBA?

Read More
Excel vba - convert string to number

So, I used the left function to take the first 4 characters of a string and I need to run a vlookup with it, but it won't find the match because it's looking through numbers. I want to do this in …

Read More