Extract number from cells

[craigfer]

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

 

[Phuoc]

Try this:

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

 

[Toadstool]

Hi Craigfer,

Does this work for you?

Craigfer.xlsx
	A	B	C
1	Data	Result as text	As number
2	BATCH 498 - P1 PACKAGE - PUMPS - MEMBRANES	498	498
3	BATCH 495 - P1 COMPRESSION (HP) - TRANSMITTER	495	495
4	P2 CRITICAL SPARES - BATCH 494	494	494
5	P2 HEATER - BATCH 499	499	499
6	Big BATCH 444554 here	444554	444554
7	Small BATCH 1 here	1	1
Sheet1

Cell Formulas
Range	Formula
B2:B7	B2	=MID(A2,FIND("BATCH ",A2)+6,ABS(FIND("BATCH ",A2)+6-(FIND(" ",A2&" ",FIND("BATCH ",A2)+6))))
C2:C7	C2	=VALUE(MID(A2,FIND("BATCH ",A2)+6,ABS(FIND("BATCH ",A2)+6-(FIND(" ",A2&" ",FIND("BATCH ",A2)+6)))))

 

[rollis13]

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]

Glad we were able to help .