vba string function?

[vbaNewby]

Hello guru's,

I want to know if there is some type of string function that satisfies this example:

myString = "hel"

Cell1: "helloworld"
Cell2: "hel loworld"
Cell3: "hel lo world"

I want to return false for cell 1 but true for cell 2 and 3. Instr function (xlpart param???) will return true for all three. I basically want to return true if "hel" is a substring but its own word within a string.

I hope there is sometype of way to do this without having to write my own function!

Thanks in advance.

 

[shg]

Consistent with your original post, it shouldn't have either.

 

[Norie]

How about any trailing/leading non-printing character?

eg CHAR(10)

 

[vbaNewby]

Consistent with your original post, it shouldn't have either.

Huh? I want that example to return true, that was Cell 2 in my original post. This formula is working correctly for you???

 

[vbaNewby]

How about any trailing/leading non-printing character?

eg CHAR(10)

No, I simply opened up a new workbook plugged in the strings in cell A1 and B1 and plugged in the formula. No hidden, non printable characters.

Besides if you need a trailing space in A1 then this function isn't going to work for me!

My search string will be "hel" not "hel "

If I can get this to work however, I can manipulate the string in VBA to put in that extra space but I can't get it to work!

 

[shg]

A1 should contain the three characters hel and nothing else.

 

[vbaNewby]

A1 should contain the three characters hel and nothing else.

Working! Sorry for the confusion!

 

[shg]

Good job.

 

[vbaNewby]

shg or norey or anyone!

Is this the same as using an excel function? Will this satisfy my 3 conditions:

InStr(1, " " & ActiveCell.Value & " ", " depreciation ", vbTextCompare) > 0</pre>

THANK YOU! As always

 

[Norie]

If you mean is InStr like SEARCH then yes.

As for the 3 conditions, what 3 conditions?

There's only one in that code.

 

[jasonb75]

Have a look at this thread, it suggests the same method for what you're trying to do, looks like sound advice to me.

http://www.mrexcel.com/forum/showthread.php?t=579448