Forum Discussion
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?
- 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.
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))))))
Seems a bit complicated
Been trying data tab followed by advanced but can't get the criteria right.
Is this a possibility?
Been trying data tab followed by advanced but can't get the criteria right.
Is this a possibility?
- If I could get pack of * in an adjacent column I could then use find and replace to remove pack of.
- 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 *
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.