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.
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 16, 2019

Kelly290 

As variant that could be

=--TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),1)),MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),1),""))

(array formula, Ctrl+Shift+Enter, for pre-DA Excel)

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

Resources