Forum Discussion

Kelly290's avatar
Kelly290
Copper Contributor
Dec 16, 2019
Solved

FIND AND COPY

I have a  huge spreadsheet with lots of  pack of qty entries in the description column. How do I set about copying the qty into a separate column on the spreadsheet? 
Formulas and Functions
  • Kelly290's avatar
    Kelly290
    Dec 17, 2019
    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.
Kelly290's avatar
Kelly290
Copper Contributor
Dec 17, 2019
Tried your formula Sergei. works fine on records ending in pack of * but get #VALUE error for records that don't end in pack of *
Kelly290's avatar
Kelly290
Copper Contributor
Dec 17, 2019
I should add that if the record does not include pack of* then the quantity will be 1
  • Kelly290's avatar
    Kelly290
    Copper Contributor
    Dec 17, 2019
    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.
  • mathetes's avatar
    mathetes
    Silver Contributor
    Dec 17, 2019

    @Kelly290 

     

    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.

  • mathetes's avatar
    mathetes
    Silver Contributor
    Dec 17, 2019

    Kelly290 

     

    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.

Resources