You are here:

# Excel/=Proper vs number and letter capitalization

Question
Hi Jan: I trust that life is treating you well.

On 2013-02-19 =Proper vs number and letter capitalization:
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

This works wonderfully - but - and there seems to always be a but...
In the cell I may have something like the following...   motor control centre 45ty57  Which I would like to be changed to Motor Control Centre 45TY57.  Your formula delivers it as MOTOR CONTROL CENTRE 45TY57.

Is it possible to Proper Case the text words but capitalize the mixed number/letter combos?

Many thanks.   BTW - I would like to make a contribution for this effort - I understand that you support Doctors without Borders.  Could I make a contribution to this organization in your name?

Hi Randy,

Hmm. I see no formula way to do that.

This used defined function appears to do the trick:

(paste into a normal code module in your workbook, save workbook as a file type that holds macro's)

Option Explicit

Public Function ProperOrUpper(Value As Variant) As Variant
Dim vStrings As Variant
Dim lStrings As Long
Dim sResult As String
vStrings = Split(Value, " ")
If IsArray(vStrings) Then
For lStrings = LBound(vStrings) To UBound(vStrings)
sResult = sResult & " " & ConvertOneString(CStr(vStrings(lStrings)))
Next
sResult = Trim(sResult)
Else
sResult = Trim(ConvertOneString(CStr(vStrings)))
End If
ProperOrUpper = sResult
End Function

Function ConvertOneString(sString As String) As String
Dim lCt As Long
Dim bHasNumber As Boolean
For lCt = 0 To 9
If InStr(sString, lCt) > 0 Then
bHasNumber = True
Exit For
End If
Next
If bHasNumber Then
ConvertOneString = UCase(sString)
Else
ConvertOneString = Application.Proper(sString)
End If
End Function

Thank you for offering to donate. Of course I welcome such a great gesture!
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, ...