Extracting Some Data

[Transitory]

Hi everyone, I have data that is formatted like this:

The real set goes on for about 1200 sets. What I need is the code after Copy: and the code after S: in separate columns to right. E.g. from the Row 1-2 entry I would get G123456789 and P 12345678. Similar for rows 3-4, nothing for row 5, and for row 11-13 G99999999 and P: 9789123. These are the 3 variants of the data. Checked in with 2 rows, Checked in with 3 rows, and unable to check in with 1 row.

Can anyone help? This is beyond my VBA skills.

 

[krogh101]

Hi,

I think you can sort it out with formulas.

How about something like this? It should be fine as long as the setup for the data is as described.
=IFERROR(IF(LEFT(B2,6)=CHAR(34)&"Book"&CHAR(34),MID(B2,FIND("Copy:",B2)+6,((FIND(")",B2)-1)-(FIND("Copy:",B2)+6))),MID(B2,FIND("S:",B2)+3,(FIND(")",B2)-1)-(FIND("S:",B2)+3))),"")

Edit: Typo