Extracting Some Data

Transitory

New Member
Joined
Sep 29, 2014
Messages
6
Hi everyone, I have data that is formatted like this:

UdbkZ6w.png


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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

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
Back
Top