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
Description field might read
CUTTING DISC 115 x 1MM PACK OF 5
or
SINGLE EDGE BLADES PACK OF 100
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 16, 2019

Kelly290 

If the quantity is always after the last space in the description, you may extract it as

=--RIGHT(A1,LEN(A1)-LOOKUP(2^10,SEARCH(" ",A1,ROW(INDIRECT("1:"&LEN(A1))))))

 

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

Resources