is there a funcion that returns specific text string in a cell?

  • Thread starter Thread starter Maria
  • Start date Start date
M

Maria

Is there a function in excel that returns a text string that follows a
specific text string in one cell to another. For example, if one cell has the
sting of words;

"www.google.com search for watch for diabetics"

is it possible to get only "watch for diabetics" copied to a new cell (the
string of words after "search for")?

Thank you so much for your help!
 
You would have to write your own using a combination of any of the following:

Instr() finds one string within another
Left() returns the left portion of a string
Mid() returns the middle portion of a string
Right() returns the right portion of a string, and
Len() returns the length of a string.

Between these five function you can handle just about any string
manipulation required. The Replace() function sometimes comes in handy as
well.

And, BTW, you are either mistakenly referring to fields as Cells, or you are
in the wrong newsgroup (this is MSAccess VBA coding, not excel). In any
case, these vba functions should be the same in excel as they are in access.

This type of custom function generally requires finding the position (as in
integer or long) and using that particular position and some length numbers
to untilize the string manipulation functions (Left, Right, Mid).

good luck!
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Maria said:
Is there a function in excel that returns a text string that follows a
specific text string in one cell to another. For example, if one cell has
the
sting of words;

"www.google.com search for watch for diabetics"

is it possible to get only "watch for diabetics" copied to a new cell (the
string of words after "search for")?
Click to expand...


Something like this ought to do it:

=MID(A1, FIND("search for ",A1)+11, LEN(A1) - FIND("search for ",A1)+11)

Although there may well be a simpler way. This newsgroup is really for
questions about coding in Microsoft Access, not Excel.
 
So I'd use InStr() if i wanted to find all 'Items' that contain "single fold"
within their 'description'. eg

SELECT db.Item FROM db WHERE db.Description InStr("single fold");

yeh?
 
Back
Top