Search for text substring

**Matt_Gerathy** · 07-20-2015, 01:14 AM

Hi guys,

First time poster and fairly new excel user, so I appreciate any help that the members here can offer.

I would like to write a formula that searches for the substring (i'm not sure if that's the right term) of text 'tHR' within cells that contain data that appears like this...

VApredictHR1L1
FeedbackLL
BVpredictLR1L1
FeedbackLR
AVpredictHR1L2
FeedbackHR

etc....

I would like it to return either a value of '1' or '0' or reading of 'true' or 'false'. Additionally, I would like to be able to drag it down the column.

The best I've come up with is...

=IF(E1="","",SEARCH("*tHR*",E1))

Which returns a value of '1' where the top line in the raw data sample above is cell E1. However this cannot be dragged down.

Thanks for any help guys. I really appreciate it as i recognise that this may not be the most advanced problem.

Matt.

**FDibbins** · 07-20-2015, 01:19 AM

Hi, welcome to the forum

there are many ways to do this, here is 1 of them...

	A	B
1	VApredictHR1L1	TRUE
2	FeedbackLL	FALSE
3	BVpredictLR1L1	FALSE
4	FeedbackLR	FALSE
5	AVpredictHR1L2	TRUE
6	FeedbackHR	FALSE

B1=ISNUMBER(SEARCH("tHR",A1,1))
Copied down

You were almost there with your formula, you just needed to error-trap...
=IF(A1="","",IFERROR(SEARCH("*tHR*",A1),""))