You are here:

# Excel/=Proper vs number and letter capitalization

Question
Greetings:

I use a simple tablet to enter location and equipment information into a form.  I "cleanup" this form data after uploading into Excel 2010 by using the =proper function- which serves the purpose quite well.   However,  some equipment contains both letters and numbers eg:  T35oplev6 - which I would like to result in T35OPLEV6 and not T35Oplev6.  I build the =proper function into a simple macro that highlights the column and then performs the = proper function.  Would you know how I could build a macro that would use perhaps both the =proper function and the = upper function when letters/numbers are involved.   My thanks for your interest Mr. Expert.  It's very much appreciated.

This seems to do the trick:

=IF(SUM(IF(ISERROR(FIND({1;2;3;4;5;6;7;8;9;0},A1)),1,0))<>10,UPPER(A1),PROPER(A1))

What it does is count the number of instances where a digit 0-9 is NOT found in the string. If that equals to 10, there are no numbers in the text and it uses Proper. Otherwise, it user Upper.

NB: if this does not work for you, replace the ; between the {} with  comma's.
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Expert advice delivered quickly. I can't imagine wanting anything more than that. Thanks - Life's good - simple as that.

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Jan Karel Pieterse

##### Expertise

Excel and Excel/VBA questions

##### Experience

Excel MVP

Organizations
Self employed Excel developer

Education/Credentials
Bachelor in Chemical Engineering

Awards and Honors
Microsoft MVP award since 2002

Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...