matching strings

[k_babb]

how can i check if a2 string is a sub string of b2

so if a2 is "Apple And pears"

and b2 is " bananna Apple And pears" this is a match

but if b2 is "Apple" should be false as it does not contain the full string of a2

 

[mikerickson]

This is case insenesitive, if you want case sensitive use FIND

=IF(ISNUMBER(SEARCH(A1, B1)), "its there", "not")

 

[JoeMo]

how can i check if a2 string is a sub string of b2

so if a2 is "Apple And pears"

and b2 is " bananna Apple And pears" this is a match

but if b2 is "Apple" should be false as it does not contain the full string of a2

If you don't want the match to be case sensitive change FIND to SEARCH in the formula.

Excel Workbook
	A	B	C
2	Apple And pears	banana Apple And pears	TRUE
Sheet3

 

[Rick Rothstein]

This is case insenesitive, if you want case sensitive use FIND

=IF(ISNUMBER(SEARCH(A1, B1)), "its there", "not")

Another way to do the case insensitive test is like this (one less function call)...

=IF(COUNTIF(B1,"*"&A1&"*"),"its there","not")

 

[mikerickson]

Another way to do the case insensitive test is like this (one less function call)...

=IF(COUNTIF(B1,"*"&A1&"*"),"its there","not")

Is a function (with letters) slower than those functions of two arguments called "operations".

ISNUMBER(SEARCH(A1, B1)) - 2 calls: ISNUMBER and SEARCH

COUNTIF(B1,"*"&A1&"*") - 3 calls: COUNTIF, & and &

(My math backround sees the difference between SUM(1,2) and 1+2 as the first uses pre-script notation and the other uses mid-script notation. )

 

[Rick Rothstein]

Is a function (with letters) slower than those functions of two arguments called "operations".

ISNUMBER(SEARCH(A1, B1)) - 2 calls: ISNUMBER and SEARCH

COUNTIF(B1,"*"&A1&"*") - 3 calls: COUNTIF, & and &

(My math backround sees the difference between SUM(1,2) and 1+2 as the first uses pre-script notation and the other uses mid-script notation. )

I see concatenation which is a pure memory/copy type activity as being different than a function call (which has to marshal the function, pass arguments, execute code that is who knows how long or involved and then finally pass back a result). Now, of course, some functions are quite fast (LEN for example) and others are quite slow (most array functions I would guess), so that could affect comparisons for competing formulas, but I really don't know how efficient each individual function actually is. My general rule for estimating formula efficiency has been the quite course measure I used here... the less function calls, the more efficient... I am sure there are many exceptions, but as a general rule, I believe that tends to work. I know there are some out there who have some "tools" which can actually measure the speed of a formula... I don't have such a tool, but that would be the way to go if fine time difference measurements between competing formulas was seriously needed.