Extracting # & following text

[tobis23]

Hi,

I have a column of text that includes numbers and text, and I am trying to extract the # to another column and the word immediately following the # to another column to separate out the sizes of the items in column A. Example:

Column A
18 oz Weiss Goblet
3/4 oz. Whiskey Glass
Beverage Tumbler, 23 Oz., 6-3/16" High, Fully Tempered, Model# 80-04137


Any suggestions?
Thank you!!!
Tobey

 

[Ron Coderre]

I suspect there are more elegant ways...but, here's what I came up with...

With
A1 containing text with
- one or more separate numbers embedded
- the first of those numbers is followed by a space, then a quantity

Examples:

18 oz Weiss Goblet
3/4 oz. Whiskey Glass
Beverage Tumbler, 23 Oz., 6-3/16" High, Fully Tempered, Model# 80-04137

This formula extracts the FIRST numeric value:

B1: =LEFT(RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
+1),FIND(" ",RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},
A1&"0123456789")))))

This formula extracts the QUANTITY after that value:

C1: =LEFT(RIGHT(A1,LEN(A1)-FIND(B1,A1)-LEN(B1)),FIND(" ",RIGHT(A1,LEN(A1)-
FIND(B1,A1)-LEN(B1)))-1)

With the above examples, the formula returns these pairs of values:

Col_B   Col_C
18       oz
3/4      oz.
23       Oz.,

Does that help?

 

[tobis23]

thank you thank you thank you thank you! if ever you are in the market for commercial kitchen supplies... please let me know and i will do whatever i can to help! amazing!