Parsing a vba's variables data

warren_s

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

one="dog"
two="cat"
three="bug"
four="bird"

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.
Warren
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

warren_s

Board Regular
Joined
May 28, 2007
Messages
73
--------------------------------------------------------------------------------

use the instr function

Can you please give me an exmple, I have no clue what that is.
Thank you.
Warren
 

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
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.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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:

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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).
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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....
 

warren_s

Board Regular
Joined
May 28, 2007
Messages
73
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.
Warren
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
You've got xx and Line the wrong way round.

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

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,150
Members
417,010
Latest member
jnuss03

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.
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 "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
Top