Extract Number Formula Needed

Dastnai

New Member
Joined
Oct 26, 2018
Messages
45
Hi Everyone,

I am looking for a formula to extract a number from a text string.

Example:

C&J ENERGY 1421633 NEAR $0 12/6/2018
O&G PLACEHOLDER 1421614 YOUNGBLOOD $0 12/6/2018
BJ SERVICES, 1421308 SCHOENHOLZ $0 12/3/2018

Goal:
1421633
1421614
1421308

Regards,

Dastnai

<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi, is it always the first number and is it always 7 digits long?

If so you could try:


Excel 2013/2016
AB
1C&J ENERGY 1421633 NEAR $0 12/6/20181421633
2O&G PLACEHOLDER 1421614 YOUNGBLOOD $0 12/6/20181421614
3BJ SERVICES, 1421308 SCHOENHOLZ $0 12/3/20181421308
Sheet1
Cell Formulas
RangeFormula
B1=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),7)


If not, try to describe the rules or provide examples that cover all the variants.
 
Last edited:
Upvote 0
Hi, is it always the first number and is it always 7 digits long?

If so you could try:

Excel 2013/2016
AB
1C&J ENERGY 1421633 NEAR $0 12/6/20181421633
2O&G PLACEHOLDER 1421614 YOUNGBLOOD $0 12/6/20181421614
3BJ SERVICES, 1421308 SCHOENHOLZ $0 12/3/20181421308

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B1=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),7)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



If not, try to describe the rules or provide examples that cover all the variants.




Works like a charm! Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top