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