parsing a text file that has start and end points

**goodman18** · 08-07-2012, 04:33 AM

ok i am new to VBA so don't really no much about it.
but basically i have 8 txt files and on each file, below you will see how they are set out, i need to get the stock number out into one field and the description into a field next to it. Will some one be able to help me. Thank you in advance.

--------- Start ---------

Stock No.00111
B50
This is the name of the stock no 00111 but they are different for each stock
This is the description of stock no 00111 all the descriptions are different

Bullet Text
¥ point 1
¥ point 2
¥ point 3

---------- End ----------

--------- Start ---------

Stock No.06222
B-EXT
This is the name of the stock no 06222 but they are different for each stock
This is the description of stock no 06222 all the descriptions are different

Bullet Text
¥ point 1
¥ pouint 2

---------- End ---------

**Richard Buttrey** · 08-07-2012, 05:33 AM

Hi,

You don't necessarily need a macro.

With your data in column A enter the following formulae and copy down.
B1:
=IF(LEFT(A1,5)="Stock",RIGHT(A1,LEN(A1)-FIND(".",A1)),"")
C1:
=IF(B1<>"",A3,"")
D1:
=IF(B1<>"",A4,"")

**goodman18** · 08-07-2012, 05:44 AM

Ah thank you so much,

Another quick question when copying it down is there a quick way to get to the bottom of the page. as theres 23000 lines

Thank you soo so much

**Richard Buttrey** · 08-07-2012, 06:21 AM

Hi,

One way is to copy the formula (Ctrl-C) , then go to the last row, (there are several ways but hitting the {End} then {Home} keys will take you to the very last row and column, then just move across to the column containing the formula, hold the shift key down to lock the cursor, then hit the {End} and then Up arrow key to select all the cells and then Ctrl-V to paste.

**goodman18** · 08-07-2012, 07:42 AM

Originally Posted by Richard Buttrey

Hi,

You don't necessarily need a macro.

With your data in column A enter the following formulae and copy down.
B1:
=IF(LEFT(A1,5)="Stock",RIGHT(A1,LEN(A1)-FIND(".",A1)),"")
C1:
=IF(B1<>"",A3,"")
D1:
=IF(B1<>"",A4,"")

ok i seem to have run into a problem

i need to be able to get the bullet points as well and there is sometimes two lines of description. So would i be able to use a formula to put everything after the name until the End point into a cell?

Thank you for you help

**Richard Buttrey** · 08-07-2012, 10:00 AM

OK, perhaps you'd better upload

a) an example text file, and
b) an Excel workbook, based on the text file that clearly shows examples of the results you expect.