View Single Post
  #3  
Old April 15th, 2009, 11:24 PM posted to microsoft.public.excel.worksheet.functions
Luciano Paulino da Silva
external usenet poster
 
Posts: 52
Default Split text file into Excel sheet and separate the final resultsinto a new sheet

Dear Glenn,
I'm having some problemas. Some of your stepes suggestions seems that
they did not work fine with me. Would it be possible that you send me
a sample sheet?
Thanks in advance,
Luciano

On 15 abr, 18:43, Glenn wrote:
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).