Excel ; Search and Substitute function

Question

Hi,
is there a way to deal with partial matches ; so in a cell I have say

'The cattle and the field'

but want to search for or substitute the words cat and dog so

     SEARCH { "cat", "dog"}, "the cattle in the field" )    = 5 , which is not what I'm wanting   

I want it ignored and only "cat" to be looked for.

is there something else I could use

Richard

Answer 1

Richard,

Basically search for " cat ", but that leads to the problem to find it in "I have a cat, you have a dog!". Therefore you have to prepare the sentence, replace all delimiters with a blank, add a blank at the beginning and end. Search for the phrase in there.

Andreas.

Answer 2

Richard,

Regex is a snail, it's a good way for a few searches if you are not a programmer and/or can not use VBA.

If you have a lot of rows... better not use it.

Andreas.

Answer 3

One way is to use the new REGEX , I know this does not give a position but works for
many scenarios

so "The cattle in the field'
"The cat and dog"

so =REGEXREPLACE(E6,"\dcat\d","TIGER") ; her "\btext\b" here \d isolates the text in the string
carrot , dollar "^ text string $" will bookend a text string .

so ; =REGEXTEST("The cattle in the field",{"cat","\bcat\b"}) = TRUE / FALSE

Answer 4

Yeah, I don't think there is a solution,
if t just contained one word I did think of

IF(LEN("cat")=LEN(H4),SUBSTITUTE(H4,"cat","TIGER"),H4)

but this is not that practical , thanks for taking the time to reply.

Richard.