Forum Discussion
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.
12 Replies
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)
- Description field might read
CUTTING DISC 115 x 1MM PACK OF 5
or
SINGLE EDGE BLADES PACK OF 100If 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))))))
Please show us how the "pack of qty entries in the description" looks like. As an example, if A1 would contain:
Box, Size A- 20 units then this formula:
=LEFT(RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1),FIND(" ",RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1),1)-1)will extract the number 20.
The formula is inspired by what I found on https://exceljet.net/formula/split-numbers-from-units-of-measure.
It will work as long as there is a space after the last digit of the number. If this is not working for you, I'm sure that some of the other contributors to this forum are able to figure out a formula. But, that's why we would need an example of what you are dealing with.
