# Extract number from cells

#### craigfer

##### New Member
I have a spreadsheet lots of people have added to it so it's inconsistent
I'm trying to extract the batch number from each cell

The number always follow the word BATCH - but it can be at the end or start (sometimes very rarely the middle)
BATCH 498 - P1 PACKAGE - PUMPS - MEMBRANES
BATCH 495 - P1 COMPRESSION (HP) - TRANSMITTER
P2 CRITICAL SPARES - BATCH 494
P2 HEATER - BATCH 499

So for above I'd like a formula that will return;
498
495
494
499

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Phuoc

##### Active Member
Try this:

=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("BATCH",A1)+5,""),"-",REPT(" ",100)),100))+0

##### Well-known Member
Hi Craigfer,

Does this work for you?

Craigfer.xlsx
ABC
1DataResult as textAs number
2BATCH 498 - P1 PACKAGE - PUMPS - MEMBRANES498498
3BATCH 495 - P1 COMPRESSION (HP) - TRANSMITTER495495
4P2 CRITICAL SPARES - BATCH 494494494
5P2 HEATER - BATCH 499499499
6Big BATCH 444554 here444554444554
7Small BATCH 1 here11
Sheet1
Cell Formulas
RangeFormula
B2:B7B2=MID(A2,FIND("BATCH ",A2)+6,ABS(FIND("BATCH ",A2)+6-(FIND(" ",A2&" ",FIND("BATCH ",A2)+6))))
C2:C7C2=VALUE(MID(A2,FIND("BATCH ",A2)+6,ABS(FIND("BATCH ",A2)+6-(FIND(" ",A2&" ",FIND("BATCH ",A2)+6)))))

#### rollis13

##### Active Member
Hi to all, I'm late but I came up with this:
=MID(A1,SEARCH("BATCH",A1)+6,3)
I supposed that your text is in column A starting from row 1, that it has a space between 'BATCH' and the number and that it is a 3 digit number, so, copy the formula in B1 and pull down.

#### rollis13

##### Active Member
Glad we were able to help .

Replies
4
Views
234
Replies
3
Views
92
Replies
4
Views
664
Replies
10
Views
598
Replies
10
Views
437

1,148,277
Messages
5,745,813
Members
423,980
Latest member
zimza

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