Create Columns from text

[umeshgiri48]

I have a dataset in which I am trying to create multiple columns from one column basis on text for eg:-

Date	Customer	Address	Vch Type	Vch No.	Debit	Narration
03.04.2019	Hasan pvt ltd	Plot no 05, Tilak road New delhi, Delhi	Sales	BPL/AT/001	2336374.00	Being the Sale of Cut Ornge Grade:"APTC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.
03.04.2019	Indra pvt ltd	112, Nelson road, Delhi	Sales	BPL/OT/002	3336374.00	Being the Sale of Cut Apple Grade:"ORTC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.
03.04.2019	Middle Venture	5th Floor, Tamouh Tower, Marina Square, Al Reem Island Abu Dhabi United Arab Emirate	Sales	BPL/PT/003	2436375.00	Being the Sale of Cut Pineapple Grade:"PATC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.

now on the basis of narration I am trying to create new column for eg:-

Date	Customer	Address	Vch_Type	Vch_No	Debit	Narration	Grade	Invoice	qty	Amount	Exch_Rate
03.04.2019	Hasan pvt ltd	Plot no 05, Tilak road New delhi, Delhi	Sales	BPL/AT/001	2336374.00	Being the Sale of Cut Ornge Grade:"APTC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.	APTC/I/66	BPL/CA/001/2019-20	12430 Kgs	$.34182.50.00	Rs.68.35
03.04.2019	Indra pvt ltd	112, Nelson road, Delhi	Sales	BPL/OT/002	3336374.00	Being the Sale of Cut Apple Grade:"ORTC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.	ORTC/I/66	BPL/CO/001/2019-20	12430 Kgs	$.34182.50.00	Rs.68.35
03.04.2019	Middle Venture	5th Floor, Tamouh Tower, Marina Square, Al Reem Island Abu Dhabi United Arab Emirate	Sales	BPL/PT/003	2436375.00	Being the Sale of Cut Pineapple Grade:"PATC/I/66" Blend Cut apple vide Invoice No.BPL/CT/001/2019-20 Dt.03.04.2019 - Qty.12430 Kgs for - $.34182.50.00 Exch.Rate:Rs.68.35.	PATC/I/66	BPL/CP/001/2019-20	12430 Kgs	$.34182.50.00	Rs.68.35

I have tried using substr and scan function but it not giving me the result because words count and delimiter's are not same

Please help me on this.

[PaigeMiller]

Find where the text Grade: is in the variable called Narration, then take the next word where the delimiters are double quotes

Example

where = findw(narration,'Grade:');
grade = scan(substr(narration,where+6),1,'"');

[Duggins · Accepted solution]

I had effectively the same approach as above, but I would probably nest all the position information unless you need to save that information. The ones below should work as long as the data you have follows the style shown in your original post. I did use two steps for Exchange Rate so that the code wasn't as wide when posting it.

grade = scan(substr(narration, index(narration,'Grade:')+6),1,'"');
invoice = scan(substr(narration, index(narration,'No.')+3),1,' ');
qty = input(scan(substr(narration, index(narration,'Qty.')+4),1,' '),best12.);
Amount = scan(substr(narration,index(narration,'$')),1,' ');
_ER = scan(substr(narration, index(narration,'Rate')+4),1,':');
ExchRate = substr(_ER,1,length(_ER)-1);