IF/ SEARCH Functions

[gldurand]

Hi Folks,

I have (Column F) that contains a string of "text" with the words "Measured" or "Non Measured" found in most of them. I have been able to categorise these records with the following formula

=(IF(SEARCH($N$1,F2)=1,"Measured","Non Measured"))


My challenge is that if there is no indication of these two words, then i get the return error of #VALUE!. I need to say that anything that is not "Measured" should be "Non Measured"

So if I have "Measured" in the first character, then should be "Measured", otherwise every other instance should be "Non Measured"

Any assistance would be appreciated

Thanks

 

[njimack]

=IF(ISNUMBER(SEARCH($N$1,F2)),"Measured","Non Measured")
or slightly shorter:
=IF(ISERROR(SEARCH($N$1,F2)),"Non ","")&"Measured"

 

[gldurand]

Does not seem to work, not giving me the results i need.

i have the following strings

Non-Measured-Customer-Environment-Power
Non-Measured-Dropped and Recovered
Measured - Customer -Phone -Helpdesk
Other
Measured - Router Config

I want to say that if String starts with Measured, then it is "Measured" otherwise it should indicate "Non Measured"

Thanks

 

[njimack]

Your use of SEARCH implied you wanted to search the entire cell content, not whether or not the cel started with specific text.

Excel Workbook
	A	B
1	Non-Measured-Customer-Environment-Power	Non Measured
2	Non-Measured-Dropped and Recovered	Non Measured
3	Measured - Customer -Phone -Helpdesk	Measured
4	Other	Non Measured
5	Measured - Router Config	Measured
Sheet1

 

[T. Valko]

Does not seem to work, not giving me the results i need.

i have the following strings

Non-Measured-Customer-Environment-Power
Non-Measured-Dropped and Recovered
Measured - Customer -Phone -Helpdesk
Other
Measured - Router Config

I want to say that if String starts with Measured, then it is "Measured" otherwise it should indicate "Non Measured"

Thanks

Try this...

=IF(LEFT(A2,8)="Measured","Measured","Non-Measured")

 

[gldurand]

Perfect thanks, works like a charm