|If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.|
||Thread Tools||Display Modes|
is there an equal fxn for 'InStr' in excel. Not Find or Search
I find "InStr" quite usefull in Access and am curious if there is an
equivalent in excel. I've tried Find and Search but they don't return the
actual string of interest, they just tell you true or false (return 1 or 0).
From Excel VBA help (about InStr VBA function):
InStr Function: Returns a Variant (Long) specifying the position of the
first occurrence of one string within another.
Syntax: InStr([start, ]string1, string2[, compare])
From Excel help (about FIND worksheet function):
FIND finds one text string (find_text) within another text string
(within_text), and returns the number of the starting position of find_text,
from the first character of within_text. You can also use SEARCH to find one
text string within another, but unlike SEARCH, FIND is case sensitive and
doesn't allow wildcard characters.
As you see, none of them returns a string (and neither does Access InStr
function) - all of them return the starting position of one string in
"Clausius" > wrote in message
> I find "InStr" quite usefull in Access and am curious if there is an
> equivalent in excel. I've tried Find and Search but they don't return the
> actual string of interest, they just tell you true or false (return 1 or
There may be better ways, but this will work.
Assuming you are looking for the string "Data" in cell C22 (containing
"All Data Available)", use this formula:
Returns "Data" if found, otherwise returns "oops"
note: a cell reference can be used instead of the actual string,
assuming the string "Data" in in cell C26:
also note that Find is case sensitive, e.g. "data" in C26 will return
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=383678
|Thread||Thread Starter||Forum||Replies||Last Post|
|Stop Excel Rounding Dates||leinad512||Excel Discussion (Misc queries)||1||April 20th 05 04:19 PM|
|Hints And Tips For New Posters In The Excel Newsgroups||Gary Brown||Excel Worksheet Functions||0||April 15th 05 05:47 PM|
|getting excel to print text equal to a column||LadyDiby||Excel Discussion (Misc queries)||0||April 11th 05 09:27 PM|
|Excel error - Startup (and Acrobat PDFMaker)||gxdata||Setting up and Configuration of Excel||0||February 4th 05 04:44 AM|
|Excel 2002 and 2000 co-install. Control Which Starts ?||cnuk||Excel Discussion (Misc queries)||2||January 17th 05 09:07 PM|