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

Resources