Extract number from cells

craigfer

New Member
Joined
May 16, 2012
Messages
25
Office Version
  1. 365
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
Joined
Apr 29, 2016
Messages
458
Office Version
  1. 2016
Try this:

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,199
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jul 30, 2012
Messages
486
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Solution

rollis13

Active Member
Joined
Jul 30, 2012
Messages
486
Office Version
  1. 2016
Platform
  1. Windows
Glad we were able to help (y).
 

Forum statistics

Threads
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.
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
Top