Selective choosing the value from a text file and filling up a row in Excel

[chiswickbridge]

My Data resides in a Notepad file in D drive in a folder called "Invest" and comprises as below :

Numeral;String1;String2;String3;Numeral2;Numeral3;Numeral4;Date

120518;-;INF209K01ZB2;Birla Sun Life 95 Fund - Dividend - Direct Plan;178.44;176.66;178.44;13-Apr-2015
120517;INF209K01ZC0;-;Birla Sun Life 95 Fund - Growth - Direct Plan;587.08;581.21;587.08;13-Apr-2015
103154;INF209K01BS7;INF209K01EE1;Birla Sun Life 95 Fund-Plan A (Dividend);157.61;156.03;157.61;13-Apr-2015
103155;INF209K01BT5;-;Birla Sun Life 95 Fund-Plan B(Growth);576.47;570.71;576.47;13-Apr-2015
131671;-;-;Birla Sun Life Dynamic Asset Allocation Fund - Direct Plan - Dividend Option;18.31;18.13;18.31;13-Apr-2015
131670;-;-;Birla Sun Life Dynamic Asset Allocation Fund - Direct Plan - Growth Option;38.79;38.40;38.79;13-Apr-2015
131665;-;-;Birla Sun Life Dynamic Asset Allocation Fund - Regular Plan - Dividend Option;17.93;17.75;17.93;13-Apr-2015
131666;-;-;Birla Sun Life Dynamic Asset Allocation Fund - Regular Plan - Growth Option;38.01;37.63;38.01;13-Apr-2015
100035;-;-;Birla Sun Life Freedom Fund-Plan A (Dividend);14.55;14.40;14.55;21-Oct-2011
100036;-;-;Birla Sun Life Freedom Fund-Plan B (Growth);30.91;30.60;30.91;21-Oct-2011

The above data always has 8 fields, separated by a semi-colon. If the data value is null, it is denoted by a hyphen.

As the Notepad file is over 10Mb and consists of over 50,000 rows, Using Get data takes a very long long time.

I need a VBA Macro to get the selective data.

I will input 103154 in my A2 cell and run the Macro and I want the macro to get the data from that row ( from Notepad file ) and put in columns B to H in proper format, eg string, Numeral and date.

In short, I will fill up A2 to A20 with the Scheme Code and run a VBA macro to get me the data only for these 18 values

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]103154[/TD]
[TD]INF209K01BS7[/TD]
[TD]INF209K01EE1[/TD]
[TD]Birla Sun Life 95 Fund-Plan A (Dividend)[/TD]
[TD]157.61[/TD]
[TD]156.03[/TD]
[TD]157.61[/TD]
[TD]13-Apr-2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Please help...

 

[jumbledore]

you can import the data using vba, see here: How to copy the text from notepad to excel - Stack Overflow and then format the data using "Text to columns" to put the delimited data in separate columns