Thursday, December 8, 2011

How To Convert All Text To Lowercase In Google Spreadsheets

We consume a lot of table-like data from clients that has a lot of human error in it. Often this is big enough that manual fix-up isn't fast enough (more than 100) but not so big that some more elaborate solution is appropriate (millions of entries needing formulas and machine learning). It's just a static table of data that needs to get loaded into a Dictionary<string, string>.

When doing so we dump the data into Google Spreadsheets, perform some fixups, and then share it with the client to verify it's accurate. One annoyance is there's no way to just switch a column to lowercase directly, so here's one way to pull it off:

  1. Select the column, right-click, and click Insert Column Right.
  2. In the first cell of the new column enter formula =LOWER(A1) (or whatever the first cell is of the original column).
  3. Copy the cell with the formula in it.
  4. Select the column and paste - Google Spreadsheets is smart enough to adjust the row number for each pasted entry. You now have a column with lowercase in it - but it requires that column to the left to still be there.
  5. Select the new column again and copy.
  6. Right-click and click Paste Special > Values only.
This wipes out the formulas. You can now delete the original column. Obviously this applies to anything you could do with a formula in spreadsheets.