How do I extract specific data from field with multiple fields in it / I have a

Question

How do I extract specific data from field with multiple fields in it /

I have a spreadsheet that I have exported data from a form I will be using on our web page. The one field I am having a problem with is the "Conference and Special Events Registration: Products" (Col BH). this is data given to me by PayPal and I am not sure how to separate what I need out of it.

I really only need the information between the parenthesis (). And I just need the info after Attendee: up to the comma then the quantity number. EX 1st Member Attendee - 1. Next I need the information between the next set of (). Info after Events: up to the comma then the quantity number. Ex Golf Lunch only - 1. I want each of these fields in a separate field in the spreadsheet (Titles of the fields are in bold on row 17.

If the Attendee is "1st Member Attendee" I would like the quantity of 1 to be in the 1st Member field.

I have not been able to figure out how to separate this field.

Here is the contents of a few cells in the Conference and Special Events Registration: Products column

Conference Registration (Amount: 0.00 USD, Attendee: 1st Member Attendee, Quantity: 1)
Conference Registration (Amount: 0.00 USD, Attendee: Addl Member Attendee, Quantity: 1)
Conference Registration (Amount: 0.00 USD, Attendee: NonMember Attendee, Quantity: 1)
Conference Registration (Amount: 0.00 USD, Attendee: Addl NonMembr Attend, Quantity: 1)
Special Events Registration (Amount: 1.00 USD, Events: Theme Dinner, Quantity: 1)
Total: 1.00

Conference Registration (Amount: 1.00 USD, Attendee: 1st Member Attendee, Quantity: 1)
Special Events Registration (Amount: 1.00 USD, Events: Golf Lunch Only, Quantity: 1)
Special Events Registration (Amount: 1.00 USD, Events: Banquet Dinner, Quantity: 1)
Manufacturers Sponsorship (Amount: 1.00 USD, Level: Bronze Sponsor, Quantity: 1)
Total: 4.00

Special Events Registration (Amount: 1.00 USD, Events: Theme Dinner, Quantity: 2)
Special Events Registration (Amount: 1.00 USD, Events: Banquet Dinner, Quantity: 1)
Manufacturers Sponsorship (Amount: 1.00 USD, Level: Gold Sponsor, Quantity: 1)
Total: 4.00

Conference Registration (Amount: 1.00 USD, Attendee: 1st Member Attendee, Quantity: 1)
Special Events Registration (Amount: 1.00 USD, Events: Golf & Lunch, Quantity: 1)
Special Events Registration (Amount: 1.00 USD, Events: Theme Dinner, Quantity: 1)
Manufacturers Sponsorship (Amount: 1.00 USD, Level: Silver Sponsor, Quantity: 1)
Total: 4.00

Explanation / Answer

%^Try this:;'

1. Select the data (sat BH2:BH50) and go to Data > Text to columns > Delimited > Next > Other > : > Next

2. In the Destination box, select an empty cell (say BI2) and click on Finish

3. Select BK2:BK50 and press Ctrl+H

4. In the Find What box, type ,* (comma and star) and click on Replace All

5. Select BL2:BL50 and press Ctrl+H

6. In the Find What box, type ) and click on Replace All

7. You may delete data in columns BI2:BJ50

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.