Copy a portion of a cell and not the whole cell

[Suavesav]

Hi,

I need a formula to copy a portion of a cell and not the entire cell into another cell. for example - A4 contains - BOOKING NUMBER: ( SA58-000000 ) ; EXTERNAL NUMBER: ( ABCDEFG000000000 ) ; FREE TEXT REFERENCE: ( Mickey ) ; IMO: ( 000000 ) ; OFS-SCM SCM REFERENCE: ( 00000000000 ) ; PRIMARY CUSTOMER REFERENCE: ( 1.0001 ) ; SHIPMENT GLOBAL ID: ( 000000000 ) ; SHIPPERS / CUSTOMERS REFERENCE: ( 1.0001 ) ; SID REFERENCE: ( 230136362236 ) .

I need to only copy the data that is underlined and in Bold to another cell. **This data is formatted differently only here and not in the actual sheet.

 

[Fluff]

How about

=TRIM(LEFT(REPLACE(A4,1,SEARCH("external n",A4)+17,""),FIND(")",REPLACE(A4,1,SEARCH("external n",A4)+17,""))-1))

 

[JvdV]

Book1
	A
1	BOOKING NUMBER: ( SA58-000000 ) ; EXTERNAL NUMBER: ( ABCDEFG000000000 ) ; FREE TEXT REFERENCE: ( Mickey ) ; IMO: ( 000000 ) ; OFS-SCM SCM REFERENCE: ( 00000000000 ) ; PRIMARY CUSTOMER REFERENCE: ( 1.0001 ) ; SHIPMENT GLOBAL ID: ( 000000000 ) ; SHIPPERS /
2	ABCDEFG000000000
Sheet2

Cell Formulas
Range	Formula
A2	A2	=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,": ( ","</s><s>")," ) ; ","</s><s>")&"</s></t>","//s[preceding::*[1]='EXTERNAL NUMBER']")