I have a mix of shelf life items that some are in months, some in years, some in quarters and some in quarters plus 1 and some unlimited.
Can these all be converted to another column in months.
Please see column E that I want the conversions to be in column F.
Thanks
i have used a nested IF
=IF(E5="","",IF(E5="Unlimited","Unlimited",IF(ISERROR(SEARCH("*Month*",E5,1)),IF(ISERROR(SEARCH("*Year*",E5,1)),IF(ISERROR(SEARCH("*QTR*",E5,1)),"X",LEFT(E5,2)*3),LEFT(E5,2)*12&" months"),LEFT(E5,2)*1&" months")))
BUT not done the Quarter +1 yet
do you have all possible combinations in your example ?
AND
5/6 years
what should that do
Before spending more time on this woul dbe good to have all possible combinations as that may change the search/nest
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Excellent thanks so much!
Based on your example
In F5 copied down
Formula:
Please Login or Register to view this content.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Please try at E6
=IFERROR(IF(LEFT(E6,2)="Un",E6,LOOKUP(9,SEARCH({"m","q","y"},E6),{1,3,12})*LEFT(E6,2)-IFERROR(-RIGHT(E6),0)),"")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks