Solutions and training for business users of Microsoft Excel.
Solutions and training for business users of Microsoft Excel.

 Excel User's Home
 ExcelUser Blog      
 Site Map              
 Contact              
 Excel for Business
 Excel Dashboards   
 Excel Solutions   
 Exploring Excel
 BI for Excel    
 Business Tools   
 Excel Catalog   
 Affiliate Program   
 Excel Help Portal  
 
   
     
   
     

Home > Exploring Excel

Convert All Entries in a Range
In Excel To Lower-Case Text


"I am trying to make a range of cells use only lowercase (it is the unit field on a purchase order). I know I can tell an individual cell to correct entries, but how do I make a range of cells function this way?" -- Lianne F.

Lianne,

This will require a macro. To keep it simple, I'm going to assume that you use the same workbook template for all your POs.

It will work like this: You will assign a range name to your Unit of Measure column. Whenever you type any character in that column, Excel will automatically correct it to be lower case.

In your PO worksheet, first assign the name "UOM" to your Unit of Measure column. To do so, select that column then...

...in Excel 2003, choose Ctrl+F3. Or choose Insert, Name, Define. Enter UOM in the Names in Workbook edit box, then choose OK.

...in Excel 2007, choose Ctrl+F3. In the Name Manager, choose New. Or  choose Formulas, Defined Names, Name Manager. Enter UOM in the Name edit box, then choose OK.

To launch the Visual Basic Editor (VBE), press Alt+F11. In the top-left corner of the VBE you'll see the Project Explorer window, shown here.

Double-click the listing for the worksheet that contains your PO. Doing so will display that worksheet's code window.

Then enter the macro shown below.

The green text is commenting, which explains each line of the macro. The remaining text is the actual code.

Because you'll probably have more horizontal space than shown here, you won't need to use the line-continuation symbols (" _" [space underscore]) shown in my code. Instead, just type those two wrapped lines in one longer line, with no spaces where they join.

Save this workbook as a template.

...In Excel 2003, choose File, Save As, and then specify the Save As Type as Template (*.xlt).

...In Excel 2007, choose Office Button, Save As, and then specify the Save As Type as Excel Template (*.xltx)

This is set up to run automatically. whenever you type anything in the UOM column, the macro should force it to be lower case. The rest of your text should be unaffected.

All the best,

Charley


 
 
 
 


ExcelUser, Inc.
http://www.ExcelUser.com

Copyright 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.

Excel Dashboards

Create professional quality dashboard reports with Excel.