Monday, April 10, 2006
Force an Excel column to upper case
This is a sweet little trick that took ages to work out.
Usually when you're working with stuff in Excel you capture raw data in one column, and then write formulae to clean that data and serve up the cleaned version in another column for use.
Sometimes though, you just can't do that. Here's a how-to for letting your users type whatever random rubbish they want into a column, and then cleaning the data right there in the cell they typed into.
For this example I'll demonstrate making a column uppercase, but the trick is fairly generalisable.
As it stands, this code will capitalise anything typed into column A, except for cell A1. You can easily change what cells and columns are covered, and what the thing actually does to cells that are included. To fiddle with which columns are covered, change line 6. Eg if you want columns C, D, E and F (ie columns 3 to 6, change it to:
If you want to affect just row 12, change line 7 to something like:
If you want to affect the whole column, you can delete lines 7 and 9 completely.. etc etc.
Usually when you're working with stuff in Excel you capture raw data in one column, and then write formulae to clean that data and serve up the cleaned version in another column for use.
Sometimes though, you just can't do that. Here's a how-to for letting your users type whatever random rubbish they want into a column, and then cleaning the data right there in the cell they typed into.
For this example I'll demonstrate making a column uppercase, but the trick is fairly generalisable.
- Make sure you've saved the workbook somewhere (weirdly, in an unsaved workbook, this trick silently fails)
- Hit Alt-F11 to get the Visual Basic editor up
- in the Project pane on the left, double-click on the name of the sheet you want to apply the effect to, eg Sheet1
- Paste the following code into the blank window that opens up:
As it stands, this code will capitalise anything typed into column A, except for cell A1. You can easily change what cells and columns are covered, and what the thing actually does to cells that are included. To fiddle with which columns are covered, change line 6. Eg if you want columns C, D, E and F (ie columns 3 to 6, change it to:
If you want to affect just row 12, change line 7 to something like:
If you want to affect the whole column, you can delete lines 7 and 9 completely.. etc etc.
