split cells

rajkrishna89 has asked for the wisdom of the Perl Monks concerning the following question:

hi monks im creating an excel sheet and im writing some data in the cells..Example


Cell A1(in excel Sheet)

main check check1
main check2 check3
main Perl Monks
[download]

I can go to Data->text to columns in the excel sheet and i can split the cells (using spaces) as follows:

A1     B1      C1
main  check   check1
main  check2  check3
main  perl    monks
[download]

Is there any way to split the data in the cells using perl?

Help out Monks

Comment on split cells Select or Download Code

Replies are listed 'Best First'.
Re: split cells by moritz (Cardinal) on Feb 20, 2012 at 11:06 UTC
Could it be that this excel file is the result of incorrectly importing a (comma/space/tab) separated file into excel? If yes, I'd recommend fixing the import in the first place (you can configure the separator in excel directly) instead of fixing up the resulting file in another step. Perl 6 - second systems done right	[reply]
Re: split cells by McDarren (Abbot) on Feb 20, 2012 at 11:00 UTC
Is there any way to split the data in the cells using perl? Yes. Open the worksheet using your favourite Excel processing module Read the data from each cell split on whitespace Write the data back into the worksheet Cheers, Darren	[reply]
Re^2: split cells by bimleshsharma (Beadle) on Feb 20, 2012 at 11:35 UTC
Yes, follow up below: * import module to parse xls. (This will help to modify your same cell of same file) * Read that cell text. * Split that cell text into newline. (@line=split '\n',cellvalue) * Split that line into tabspace (@innerline=split '\t',$line(index) * Now write each index position value of @innerline into different cells. Hope this will help.	[reply]