Excel Data Sorting Tricks
I've prepared a lot of Excel data that has been exported from back office systems and Customer Relationship Management systems over the years to prepare it for PDF automation projects.
In my experience the data is messy and inconsistent more times that not. While not specific to Acrobat or JavaScript, I thought I would share some tips and tricks for organizing Excel data.
Automatic Column Width Sizing
The column widths in the spreadsheet with the exported data are usually the default width, leaving lots of data covered up by the next column. Follow these simple steps to automatically increase all column widths so no data is cut off by another column:
Click the square above the 1 in the row labels and to the left of the A in the column labels. This will select the entire sheet.
Select All Double click any of the lines the separate the column labels.
Paste Special
After copying a cell or cells to the clipboard in Excel, right-clicking and selecting Paste Special opens a dialog window with several options.
Selecting only Values will paste the values of the fields, not the formulas they contain (if any), or formatting. I use this a lot after I create a formula and drag it down to all cells in the column. I immediately copy the column then paste-special-values to another column, or over itself, to change formulas into the results only (see video at the end of this post).
A cell that is formatted a certain way can be copied, and then paste-special-formats can be used to copy that formatting to multiple cells.
Transpose can be used to convert a row into a column, or a column into a row.
Formulas For Cleaning Up Data
=TRIM(text) removes any white space from both ends of the text in a cell.
=LOWERCASE(text) converts all characters in the cell to lower case.
=UPPERCASE(text) converts all characters in the cell to upper case.
=PROPER(text) capitalizes the first letter of each word in the cell and sets the remaining letters to lowercase.
Removing Unwanted Phrases
If often encounter columns with the names of people that are inconsistent, with some that contain titles like Mr., Mrs., Dr., etc. and others that do not. The titles need to be removed so we eventually end up with two columns: First Name and Last Name.
Select the column and press Ctrl + h to open the find and replace window. This keystroke shortcut is the same for Microsoft Word and Notepad as well. In the Replace tab enter one of the phrases in the Find what field and leave the Replace with field empty. If removing Mr. and the space after it, type Mr. and the space then click Replace All.
A message will tell you how many replacements were made.
Spitting Text Into Columns
Suppose you have a column with cells containing first name -space- last name, and you need to split the first name and last name into two separate columns so you can sort by last name. Follow these steps:
Go to the Data tab in Excel.
Select the column containing the names.
Click the Text To Columns button.
Select Delimited and click Next.
Check space as the delimiter. As you can see in the next image, some first names are split with a space, as well as some last names.
Click Next.
Click the destination arrow and then select three empty columns for all the corresponding rows by dragging the cursor over the cells.
Click Finish. Due to some first names and last names being split with a space you will have to do some manual clean up joining these back together from the three columns they were spit to.
Join Cells With A Formula
I often use Excel and its formula capability to write JavaScript. Here's an example: