LinkBack URL
About LinkBacks
I need a formula that will return the contents of C1 as long as A1 does not contain the text "Q01" or "C01". The code will be placed in cell D2
I need a formula that will return the contents of C1 as long as A1 does not contain the text "Q01" or "C01". The code will be placed in cell D2
Cell D2:Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I tried this formula but i just showed the contents of C1 for everything. Im not sure if it matters but A1 with either contain one or the other, not both.
Last edited by RaydenUK; 03-03-2015 at 11:57 AM.
The formula is perfect
This formula would give you value of C1 as long as you dont have Q01 or C01 in A1 ...for any Q01 or C01 in cell A1 it would give a blank
Happy to Help
How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html
"I don't get things easily, so please be precise and elaborate"
If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.
Sourabh
If A1 contains "C01", or A1 contains "Q01" (without quotes), you will get a null return. If it does NOT contain either of those string values, you will get the contents of C1.
That is what I understood you to be asking for.Regards, TMSI need a formula that will return the contents of C1 as long as A1 does not contain the text "Q01" or "C01". The code will be placed in cell D2
Ok i fooled around with it and the problem is that i also have other characters in those cells as well, not just C01 or Q01. So if those characters are present anywhere in the cell then do not show C1
The formula does what you asked it to do. We cannot second guess what other data might, or might not, be in the cell.
Perhaps you need to restate the question.
Yea your right sorry for the confusion. Then the formula does not need to show the contents of C1 if A1 contains within the text "Q01" or "C01". A1 may look like "Q01blahblah" or "C01blah". If so then do not return contents of C1 in D1 where the formula goes.
Try:Formula:Please Login or Register to view this content.
HA! perfect! thank you very much and again sorry for the confusion.
You're welcome. Thanks for the rep.
You also said you wanted the formula in cell D2, not that it really matters
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
Or this
=IFERROR(IF(LOOKUP(2^15,SEARCH({"Q01","C01"},A1)),""),C1)
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
@AlKey: neat ... and the SEARCH is case insensitive, which may be an advantage.
Regards, TMS
Thank you TrevorOriginally Posted by TMS
@Alky: you're very welcome; we all learn from each other
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks