Need to Extract Data from Long Text String

[usman_excel]

Hi,

I need the data in column B segmented out into each their own column in the spreadsheet. Each row is a reservation record. I have created columns in front of column B from column C to column I for the items that may appear in column B.

Columns would be Reservation Type, Amount, Quantity, Discount, Total, Coupon Used, Book Quantity and Book Amount
Here are the options for Reservation Type: Products
Clergy Dinner Reservation
Standard Dinner Reservation
Head Table Dinner Reservation
Table Reservation (Party of 10)
Sponsor a seat
Book

http://prnt.sc/d692rv

Thanks in advance!

Best Regards,
Usman

 

[AVK]

Dear usman : Try below. Formula in "B2"
=LEFT(A2,FIND("(",A2)+500)&","&C2&","&D2&","&E2&"%"&G2&","&H2&","&I2

 

[p45cal]

2 of the 7 sample reservation records have 2 Reservation Types, 2 Amounts, 2 Quantities; how do you want to handle these?

 

[azumi]

Hope it help, but i still confused with book amount and book quantity what different with amount and quantity?

regards

 

[usman_excel]

Dear usman : Try below. Formula in "B2"
=LEFT(A2,FIND("(",A2)+500)&","&C2&","&D2&","&E2&"%"&G2&","&H2&","&I2

Sorry Dear, This is not working for extraction of required data.

 

[usman_excel]

2 of the 7 sample reservation records have 2 Reservation Types, 2 Amounts, 2 Quantities; how do you want to handle these?

Yes You are right, as it has Book Name, Book Amount and Book Quantity and I need to extract book amount and book quantity in its respective column. Please review and update. Thank you!

 

[AVK]

Usman : Try in "C2" (Amount)
=MID($A2,FIND("Amount: ",$A2)+8,6)
In "G2" (Book Quantity)
=MID($A2,FIND("Quantity: ",$A2)+10,1)
& copy paste upto last record

 

[usman_excel]

Usman : Try in "C2" (Amount)
=MID($A2,FIND("Amount: ",$A2)+8,6)
In "G2" (Book Quantity)
=MID($A2,FIND("Quantity: ",$A2)+10,1)
& copy paste upto last record

Yes great. That works.