View Single Post
  #9  
Old April 18th, 2009, 02:00 AM 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 think that it will be necessary that I find another strategy since
my text files are as large as that one I sent you.
Despite I use Excel 2007, I think that it will not be possible perfom
this operation for all cases.
Thank you very much for all your help.
Luciano

On 16 abr, 19:26, Glenn wrote:
That file has over 870,000 lines of data in it, if I'm reading it correctly.
I'm using Excel 2003, which only has 65,536 rows, so I can't even process it all
at once. *A smaller sample would have been sufficient.

Having said that, if I open your document in Word and copy a large chunk of data
into my spreadsheet, it works exactly as expected.

http://www.savefile.com/files/2079526

Luciano Paulino da Silva wrote:

It is one of my files:
http://www.savefile.com/files/2079390
Thanks in advance,
Luciano


On 16 abr, 15:05, Glenn wrote:
There were no limits to the solution I provided, unless your data is
significantly different than your example. *Save a copy of your actual data towww.savefile.comandI'll look at it.


Luciano Paulino da Silva wrote:


Dear Glenn,
Thank you very much for your file.
The spaces are inserted. However, my data have hundreds of headers and
consequently data, but I can not understand how could I put them to
the PivotTable. The number of headers is variable for each situation.
Thanks in advance,
Luciano
On 16 abr, 11:13, Glenn wrote:
Luciano Paulino da Silva wrote:
Dear Glenn,
I'm having some problems. 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
http://www.savefile.com/files/2079043
I had to make some small modifications to the formulas to handle leading spaces
in the data I copied from your original message. *Not sure if they are present
in the actual data or not, but if they are it should work now.
C2 = IF(D2="","",IF(C1="",A1,C1))
D2 = IF(ISERROR(FIND(" ",A2)),"",TRIM(LEFT(TRIM(A2),
FIND(" ",TRIM(A2)))))
E2 = TRIM(MID(SUBSTITUTE(SUBSTITUTE(TRIM(A2)," ",
REPT(" ",99),2)," ",REPT(" ",99),1),99,99))
F2 = TRIM(MID(SUBSTITUTE(SUBSTITUTE(TRIM(A2)," ",
REPT(" ",99),3)," ",REPT(" ",99),2),99,99))
G2 = TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99),3),99,99))