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).
|