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]

How about a formula?

=ISNUMBER(SEARCH(" " & myString & " ", " " & A1 & " "))

 

[vbaNewby]

A formula?

=ISNUMBER(SEARCH(" " & myString & " ", " " & A1 & " "))

Thanks for the quick reply.

I don't think this will satisfy all conditions however. So what you have here is you are searching " hel " starting the search after a blank space in a cell...

What if A1 is this:

"hel loworld"

I would need this to return true, unless I am mistaken, this won't with your formula?

 

[shg]

You tried it, and ...?

 

[Scott Huish]

Why wouldn't it?

Excel Workbook
	A	B	C
1	hel loworld		TRUE
Sheet1

 

[vbaNewby]

You tried it, and ...?

Unless I'm not using the formula correctly...

Cell A1 -> "hel"
Cell B1 -> "hel loworld"

Formula -> =ISNUMBER(SEARCH(" " & A1 & " ", " " & B1 & " "))

Results: False

 

[Scott Huish]

Make sure your cell references are correct. That formula returns TRUE. Also check for any extraneous spaces.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 98px"><COL style="WIDTH: 117px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>hel</TD><TD>hel loworld</TD><TD style="TEXT-ALIGN: right">TRUE</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C1</TD><TD>=ISNUMBER(SEARCH(" " & A1 & " ", " " & B1 & " "))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[vbaNewby]

???? Must be going crazy

 

[shg]

Got a trailing space in A1?

 

[vbaNewby]

Got a trailing space in A1?

Yes! Still a no go. Tried a trailing space, a leading space and both. Ugh.