Expert: Tom Ogilvy - 11/10/2009

Question
Hi,

I am trying to extract data from within a cell that contains a massive string.  For example, in cell A1 it might contain: "This is a sample of data within this cell"

I need to figure out how to extract parts of the string, such as just the word "sample" by searching through the cell A1.  The word sample would then be but into another cell such as C4.

Can you help at all?  I appreciate the effort and time you took to read this request.

Answer
TX

technically yes, you can do that

cell C4:
=Mid(A1,find("sample",A1),6)


But that seems meaningless to me since I am returning the same thing I searched for.  But you know what you want to do.  I tested the above with a 12,041 character string and it worked fine.

If you just want to test if the string contains sample, then display sample you could do

=if(countif(A1,"sample")>0,"sample","")

now if you wanted to key on sample and extract the next 10 characters after the end of sample

=MID(A1,FIND("sample",A1)+7,10)

that would display   "of data wi"

as an example.

If I didn't understand the question, then post a follow-up.

--
Regards,
Tom Ogilvy