Forum Discussion
FIND AND COPY
- Just tested your formula on the entire spreadsheet.
AMAZING! It works a treat.
Does exactly what I wanted.
Thank you so much for your help.
That could be several variants, but the main point base on which logic to extract the number. My assumption was the qty is the number at the end of the text separated from it by space. Doesn't matter which text is before. We may wrap formula by IFERROR() for the case when there is no number at the end and return zero in such case:
as
=IFERROR(--RIGHT(A1,LEN(A1)-LOOKUP(2^10,SEARCH(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))))),0)For the same logic it could be other variants of formula, but for another logic it definitely shall be another formula.
- I should add that if the record does not include pack of* then the quantity will be 1
Here's a formula that works. Assume your text is in Cell B4, then this in C4 will get the number as a value:
=IFERROR(VALUE(RIGHT(B4,LEN(B4)-FIND("PACK OF",B4)-7)),1)
Now, it only works if "PACK OF" is always there and in caps. We could add an "OR" clause if it sometimes is in lower case.
And if there is no reference to "PACK OF" this returns the value 1.
See attached.
Overnight it occurred to me that this use of UPPER would resolve the issue of upper or lower case references to "pack of"...it will even work with "pAcK oF"
=IFERROR(VALUE(RIGHT(B4,LEN(B4)-FIND("PACK OF",UPPER(B4))-7)),1)
By the way, I hope you are changing the procedure by which this information is collected (assuming it's an ongoing process) so that you head off these semi-useless entries before they occur.
