brothertruffle880
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?
How can I do this?
Can you give a "before" and "after" example of what you want?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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")
~bp
Bill, you could also use the UPPER function...
=UPPER(A1)
=UPPER(A1)
Wayne,
Per the requestor:
~bp
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
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
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
Assuming your cell starts in A1, then type below formula in B1:=RemoveSpecial(A1)
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(OldFormul a)
Thanks
Rob H
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(OldFormul
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
This is like yesterday in the SQL Server forum, when someone asked for a way to take absolute values without using the ABS() function :)