Tuesday, May 3, 2011

Parsing cells in Excel

In the case you need to parse the cells in a column or multiple columns in Excel 2003:


Select the cells to be parsed.
Click Data/Text to columns.
1)
If there is a separator symbol that could be used for parsing:
Select Delimited,
Type in the symbol that wil be recognized as a separator,
Choose Destination cell for the output




The resulting output for the given example:



2)
In the case the cells' content has fixed width one can set a position of a separator:
Select Fixed Width,
Place the separators where needed to split the cells


The resulting output for the given example:


Sorting lines in Notepad++

In Excel 2003 for sorting the rows using the desired column use Data/Sort option. However, you may end up with a problem of being able to sort only files with less then 65,536 rows in Excel 2003. Newer versions of Excel and OpenOffice have the extended limit of 1,048,576 rows. Even in that case you may want to analyze bigger files with more than 1,048,576 rows.

One possible way to sort according to the first column:
Install Notepad++, http://notepad-plus-plus.org/
Click Plugins/Plugin Manager/Show Plugin Manager

Under Available find TextFx Characters
Click Install
Selext rows to be sorted.
Click TextFx/TextFx Tools
Check or uncheck Sort ascending
Click Sort lines case sensitive or Sort lines case insensitive