View Single Post
  #2  
Old April 15th, 2009, 10:43 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default Split text file into Excel sheet and separate the final resultsinto a new sheet

Luciano Paulino da Silva wrote:
Dear All,
I would like to split one text file (example bellow) containing
several packs of data with the head "MW-....." into one excel file
for which each sheet should contain the data bellow the "MW... "
separated into four columns. After that, I will have to count the
number of times that apear each one of the strings present in the
columns (For example, to the sheet MW-
Silks_1_10-071f53284b36f9841994574243ecb063, it appears on column 3
the string coil 4 times.
Finally, the output should be something like:
For column 3:

coil turn helix bend bridge sheet
MW-Silks_1_10-071f53284b36f9841994574243ecb063 4
1 5 0 0 0
MW-Silks_1_10-07ab959b2314cae1f575921c5b0f7bce 6
0 0 6 1 0
MW-Silks_1_10-0c045e18accfb4d3c78af87e0fbda543 4
1 0 3 0 13

File example (simplified since they have until 500 kb of data):

MW-Silks_1_10-071f53284b36f9841994574243ecb063

255 ALA coil

256 ALA coil


SNIP





Somebody could help me?



Put your data in a blank worksheet starting in A2. Put column headers in C1:G1
as appropriate (I used "Header", "Column1", "Column2", "Column3" and "Column4").
In C2:G2, enter the following formulas and copy down as needed:

C2 = IF(D2="","",IF(C1="",A1,C1))

D2 = IF(ISERROR(FIND(" ",A2)),"",TRIM(LEFT(A2,FIND(" ",A2))))

E2 = TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2," ",REPT(" ",99),2)," ",
REPT(" ",99),1),99,99))

F2 = TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2," ",REPT(" ",99),3)," ",
REPT(" ",99),2),99,99))

G2 = TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99),3),99,99))

Place your cursor in C2 and select Data / PivotTable and PivotChart Report and
then click Finished.

From the PivotTable Field List drag "Header" to the Row Fields area and drag
"Column3" to the Column Fields area and the Data Items area. In the drop down
for "Column3" in the Column Fields area, uncheck the blank data. You can drag
the individual column headers to get them in the proper order, if that is
necessary (default is alphabetical).