Link to home
Start Free TrialLog in
Avatar of brothertruffle880
brothertruffle880Flag for United States of America

asked on

excel 2010 - Formatting Text in a cell to capitalize without using UPPER function

I have text in a group of cells which I would like to capitalize but I don't want to use the upper function.
How can I do this?
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Can you give a "before" and "after" example of what you want?
ASKER CERTIFIED SOLUTION
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bill Prew
Bill Prew

If you need to do it in a formula here is a brute force approach.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"a","A"),"b","B"),"c","C"),"d","D"),"e","E"),"f","F"),"g","G"),"h","H"),"i","I"),"j","J"),"k","K"),"l","L"),"m","M"),"n","N"),"o","O"),"p","P"),"q","Q"),"r","R"),"s","S"),"t","T"),"u","U"),"v","V"),"w","W"),"x","X"),"y","Y"),"z","Z")

Open in new window

~bp
Bill, you could also use the UPPER function...

=UPPER(A1)
Wayne,

Per the requestor:

but I don't want to use the upper function

~bp
Can I ask, as nobody else has, why do you not want to use UPPER function???

The only thing I can think, the cells are already populated so you would have to use UPPER function in another range of cells and then copy and paste values back to the original range. Any formula based solution would be the same.

Using Wayne's suggestion would overcome this but, as I often point out, when a VBA routine has been run the Undo history is cleared, ie the capitalization cannot be undone by simply clicking the Undo button.

Something I have often thought missing from Excel is a feature which is included in Word, the ability to change the case of a string of text; upper, lower and proper.

Thanks
Rob
Rob,

After I posted and saw Wayne's I started to "assume" that I had read it wrong, and that Wayne had interpreted correctly.  Not using UPPER makes more sense in the context of doing one or more cells in place without changing the formula, etc.  So hoping the poster will clarify, but I'm think Wayne may have read it clearer than I did...

And I agree that forfeiting undo in a case like this is a big compromise.

~bp
Optionally you can use below function:
Function RemoveSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?™""®<>|.&@# (_+`©~);-+=^$!,'" 'This is your list of characters to be removed
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")

    Next
    sInput = UCase(sInput)
    RemoveSpecial = sInput
End Function

Open in new window

Assuming your cell starts in A1, then type below formula in B1:
=RemoveSpecial(A1)

Open in new window

Added VBA in your topic, as without any function or vba code its difficult to get your desired result.
Using a User Defined Function (UDF) still has to be used in the same manner as a formula, therefore may as well use UPPER function.

Other issue which may not have been considered; the contents of the cells are text as per the question but are they as a result of formulae or typed. If as a result of formulae, anything we do will overwrite the formulae with values unless the formulae can be tweaked to include UPPER function at the start, eg VBA to do:

NewFormula=UPPER(OldFormula)

Thanks
Rob H
Another approach would be to have below code in your worksheet change event, as you type, it will automatically capitalize the text:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 
Application.EnableEvents = False
 
If Target.Column = 1 Then 'Change the target column where you want to have change
 
Target = UCase(Target)
 
End If
 
Application.EnableEvents = True
 
End Sub

Open in new window

This is like yesterday in the SQL Server forum, when someone asked for a way to take absolute values without using the ABS() function :)