# Extracting # & following text

#### tobis23

##### New Member
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

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Ron Coderre

##### MrExcel MVP
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:
Code:
``````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:
Code:
``````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:
Code:
``````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:
Code:
``````Col_B   Col_C
18       oz
3/4      oz.
23       Oz.,``````
Does that help?

#### tobis23

##### New Member
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!

Replies
7
Views
253
Replies
7
Views
214
Replies
20
Views
1K
Replies
10
Views
601
Replies
15
Views
481

1,191,581
Messages
5,987,460
Members
440,097
Latest member
Wint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back