I need to extract text from a long text field

[Jeffrey Green]

Sample data from a cell - I have thousands of rows, same format . . .</SPAN>

ERROR: Transaction Num (1135653-EPKTA-38-054919000): Version Num (1.0): Invoice Num (1J12323-EPKTA): Product ID (CT16-V04100-ABN4C6): Supplier (EPKTA): The Unit value on the CRI line item is missing. </SPAN>

I only need that Product ID, and disregard the rest!

I would prefer something to extract ONLY whatever lies between the ( and the ) following the words Product ID.

This report is system generated and always follows the same format.

It would be ok to remove unwanted text from within the same column, or maybe even better, to insert the extracted text into the adjacent column.

I can move that column to Column A if needed, etc.

Any suggestions?</SPAN>

 

[Oeldere]

With text to collumn I get (CT16-V04100-ABN4C6) in collumn M.

If you have data similar to that, you can delete the not wanted collumns.

 

[Jonmo1]

Try

=MID(A1,FIND("Product ID",A1)+12,FIND(")",A1,FIND("Product ID",A1)+12)-(FIND("Product ID",A1)+12))

 

[tigeravatar]

Alternate:

=TRIM(MID(SUBSTITUTE(MID(A1,FIND("(",A1,SEARCH("product id",A1))+1,99),")",REPT(" ",99)),1,99))

 

[T. Valko]

Sample data from a cell - I have thousands of rows, same format . . .</SPAN>

ERROR: Transaction Num (1135653-EPKTA-38-054919000): Version Num (1.0): Invoice Num (1J12323-EPKTA): Product ID (CT16-V04100-ABN4C6): Supplier (EPKTA): The Unit value on the CRI line item is missing. </SPAN>

I only need that Product ID, and disregard the rest!

I would prefer something to extract ONLY whatever lies between the ( and the ) following the words Product ID.

This report is system generated and always follows the same format.

It would be ok to remove unwanted text from within the same column, or maybe even better, to insert the extracted text into the adjacent column.

I can move that column to Column A if needed, etc.

Any suggestions?</SPAN>

Is the number of characters within the (...) always the same?

Product ID (CT16-V04100-ABN4C6)

CT16-V04100-ABN4C6 = 18 characters

 

[Jeffrey Green]

No, Valko...the Product ID is varying lenths, and technically may not have a prefix, such as -29233-AB

 

[Jeffrey Green]

Jon and Tiger, thanks . . .both solutions were perfect!

(thanks to all)

Closed

 

[T. Valko]

No, Valko...the Product ID is varying lenths, and technically may not have a prefix, such as -29233-AB

Try this...

=LEFT(MID(A2,SEARCH("Product ID (",A2)+12,255),FIND(")",MID(A2,SEARCH("Product ID (",A2)+13,255)))