Parsing a vba's variables data


Board Regular
May 28, 2007
I have 4 variables with words in them for example


I have a 5th variable with a sentence in it for example

Sen=”The bug and bird are in the park”

What I need to know is which of the variables content appears in the variable Sen first and what position it is. In this case it would be variable three position 5.

Does anyone have an idea how to do this in VBA, I would appreciate your suggestions.
Thank you very much in advance.

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

use the instr function

Can you please give me an exmple, I have no clue what that is.
Thank you.
Upvote 0
This is a vba function.
I think "search" is an equivalent "worksheet function", but I have never used it since I mostly parse things in vba.
Upvote 0
Instr Searches for a string within another string. Returns the position number it's found in. Returns 0 if not found. It Works Almost exactly the same as search or find worksheet functions...

Syntax is like this

Instr(Start Postion,String Searched In,String Searched For)

Start position is optional, defaults to 1

so something like this, using your variables..

If Instr(1,Sen,One) > 0 Then

it is case sensitive, to make it NOT case sensitive, use the the UCase Function..

If Instr(1,Ucase(Sen),Ucase(One)) > 0 Then

You would have to construct a loop or something to go through all four of your variables....

Hope this helps..
Last edited:
Upvote 0
You can use the 4th argument of Instr to specify what kind of comparison you want to use (eg vbBinaryCompare = case sensitive, vbTextCompare = case insensitive).
Upvote 0
Ahh, thanks Richard.

for some reason, I only read the help files enough to learn what I needed to use instr. So I never read far enough to learn what the 4th argument was for...Guess that's what I get...LOL.
Upvote 0
This is the definition of the 4th argument in the Help Files...

<TABLE cellSpacing=4 cols=2 cellPadding=4><TBODY><TR vAlign=top><TD class=T width="16%">compare</TD><TD class=T width="84%">Optional. Specifies the type of string comparison. If compare is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison. Specify a valid LCID (LocaleID) to use locale-specific rules in the comparison.</TD></TR></TBODY></TABLE>

Not very helpfull if you ask me....

But if you click on the hyperlink for "string comparison" it takes you to this..

string comparison

A comparison of two sequences of characters. Use Option Compare to specify binary or text comparison. In English-U.S., binary comparisons are case sensitive; text comparisons are not.

That probably would have helped....
Upvote 0
Line = "asd."
xx = "."

posit = InStr(1, xx, Line)
Hi Again,
I must be doing something wrong. I put the above code in and posit keeps coming up zero and it should come up 4.
Can you please tell me what I am doing wrong.
Thank you.
Upvote 0
You've got xx and Line the wrong way round.

By the way you don't really need the 1, that's a default.:)
Upvote 0

Forum statistics

Latest member

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
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
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