MID and SEARCH formula to extract text from a larger string

[tewellt]

I have a cell (E1) with a string of text. I need to extract the batch number from the text string and put it in a seperate cell (D1) The problem I am having is the batch number ie either 7 or 8 characters in lentgh. Here is a sample of the alue in E1:



BatchID - BI98C01D XMLExtraction - FATAL error occurred while processing

BatchID - BI352C3 XMLExtraction - FATAL error occurred while processing



Here is my Code to pull the batch number out:



This one works when the batch number is 8 positions but not when it is 7 postions. It adda a space at the end

=IF(E150="","",MID(E150,SEARCH("-",E150)+2,SEARCH(" ",E150)-0))

This one works when the batch number is 7 positions but not when it is 8 postions. It truncated the last position of the batch number,

=IF(E179="","",MID(E179,SEARCH("-",E179)+2,SEARCH(" ",E179)-1))

I have normalized the data to ensure there were no leading spaces



This all leads to my COUNTIF to be off when counting the number of 7and 8 position batch numbers.

=CONCATENATE(COUNTIF($D$7:$D300,"????????")," -  Batches with 8 character batch id: ")


=CONCATENATE(COUNTIF($D$7:$D300,"???????")," -  Batches with 7 character batch id: ")

Any help in getting these formulas fixed would be greatly appreciated.

Terry

 

[JoeMo]

Re: Need help with MID and SEARCH formula to extract text from a larger string

Here's another approach. Assumes the ID always follows " - " and the string always begins with "BatchID".

Excel Workbook
	A	B
1	BatchID - BI98C01D XMLExtraction - FATAL error occurred while processing	BI98C01D
2	BatchID - BI352C3 XMLExtraction - FATAL error occurred while processing	BI352C3
Sheet1

 

[Rick Rothstein]

Re: Need help with MID and SEARCH formula to extract text from a larger string

If your text always starts with "BatchID - " and if there is always a space after the batch number and if, as you say, the batch number is either 7 or 8 characters long, then you can use this simple formula...

=TRIM(MID(A1,11,8))

If the text does not always start with "BatchID - ", then if the batch number always follows the first dash and is always surround by only one space on either end, then this should work instead...

=TRIM(MID(A1,FIND("-",A1)+2,8))

 

[tewellt]

Re: Need help with MID and SEARCH formula to extract text from a larger string

Thank you for the information. I used the Trim and it works perfectly.

 

[tewellt]

Re: Need help with MID and SEARCH formula to extract text from a larger string

Sorry for the slow response. Thank you for suggesting using the Trim function. I did and it is working perfectly.