Hi,
What is the cleanest way of having an if formula where if cell A1 begins with either a 1,2,3,4,5 then return “Bals” if not return “OPER”?
Paul
Hi,
What is the cleanest way of having an if formula where if cell A1 begins with either a 1,2,3,4,5 then return “Bals” if not return “OPER”?
Paul
Hi there. Try this, which will work with alphanumeric cells (you didn't specify if that was the case) and will NOT return the wrong result if the first alphanumberic character is zero:
=IF(AND(CODE(LEFT(A1,1))>48,CODE(LEFT(A1,1))<54),"Bals","Oper")
Last edited by Glenn Kennedy; 05-06-2015 at 12:45 PM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hi
Formula:Please Login or Register to view this content.
And if the first character will always be a digit then
Formula:Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
OR
=IF(MIN(SEARCH({0,1,2,3,4,5},A1&1/17))=1,"Bals","OPER")
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
Formula:Please Login or Register to view this content.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Hi All
wow - so many great responses! Thank you all
Glad to have helped and thanks for the rep.
Likewise...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks