You are here:

Excel/=Proper vs number and letter capitalization

Advertisement


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?

Answer
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!
About Excel
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


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, ...

©2016 About.com. All rights reserved.