LinkBack URL
About LinkBacks
SharpL 
Hi,
I want a forumla that states IF cell F5 contains "paint" then return "@PAINT"
F5 may contain other values as well besides 'paint' so =if(f5="paint","@PAINT","") won't work.
Many thanks
Forumla help for SEARCH and IF
Hi,
I want a forumla that states IF cell F5 contains "paint" then return "@PAINT"
F5 may contain other values as well besides 'paint' so =if(f5="paint","@PAINT","") won't work.
Many thanks
Try:
Formula:
=IF(ISERROR(SEARCH("paint",F5)),"","@PAINT")
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Thank you
I have created the below:
Formula:=IF(ISERROR(SEARCH("paint",F64)),IF(ISERROR(SUBSTITUTE(F16," ","")*SEARCH("@JT",G64)),IF(ISERROR(SEARCH("transport",F64)),"","@TRANSPORT"),"@SUBCON"),"@PAINT")
However the @SUBCON part isn't working when G64 contains "@JT", so long as G64 has no spaces (spaces " " substituted for "")
Any advice?
Last edited by SharpL; 10-10-2014 at 06:31 AM.
Should be:
Formula:=IF(ISERROR(SEARCH("paint",F64)),IF(ISERROR(SUBSTITUTE(G64," ","")*SEARCH("@JT",G64)),IF(ISERROR(SEARCH("transport",F64)),"","@TRANSPORT"),"@SUBCON"),"@PAINT")
But still doesn't work?
Last edited by SharpL; 10-10-2014 at 07:23 AM.
What are you trying to achieve with this part:
Formula:IF(ISERROR(SUBSTITUTE(F16," ","")*SEARCH("@JT",G67))
Do you want to return "@SUBCON" if G67 contains "@JT" and F16 is a number? As that is how it is currently evaluating...
One sec, just noticed that. I'll ammend now.
And still doesn't explain what you are trying to achieve....
Explain in simple terms which criteria you want to return "@SUBCON"
Apologises.
Forumla =
If F64 contains 'paint', return '@PAINT'
If F64 contains 'transport', return '@TRANSPORT'
If G64 contains '@JT', return '@SUBCON' - I need to remove spaces here via Substitute(G64," ","") to ensure '@JT' is captured, as this may return as any variation of "@ JT".
Then try:
Formula:=IF(ISERROR(SEARCH("paint",F64)),IF(ISERROR(SEARCH("transport",F64)),IF(ISERROR(SEARCH("@JT",SUBSTITUTE(G64," ",""))),"","@SUBCON"),"@TRANSPORT"),"@PAINT")
Perfect, thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks