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 16, 2019
Seems a bit complicated
Been trying data tab followed by advanced but can't get the criteria right.
Is this a possibility?
Kelly290's avatar
Kelly290
Copper Contributor
Dec 16, 2019
If I could get pack of * in an adjacent column I could then use find and replace to remove pack of.
  • 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 *
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 17, 2019

      Kelly290 

      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.

    • 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
      • 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