automatically changing text case

[Guest]

I am merging several different excel spreadsheets and some of the names are in all uppercase and some are first initial of name in uppercase and the rest in lowercase. Is there an easy way (as in Word) to highlight all the names in uppercase and automatically change them to first letter uppercase, rest lower case???

 

[Paul B]

Susan, here is a macro by Ivan F Moala that will do what you want

Sub TextConvert()
'By Ivan F Moala
'will change the text that you have selected,
'if no text is selected it will change the whole sheet
Dim ocell As Range
Dim Ans As String

Ans = Application.InputBox("Type in Letter" & vbCr & _
"(L)owercase, (U)ppercase, (S)entence, (T)itles ")

If Ans = "" Then Exit Sub

For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
Select Case UCase(Ans)
Case "L": ocell = LCase(ocell.Text)
Case "U": ocell = UCase(ocell.Text)
Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
LCase(Right(ocell.Text, Len(ocell.Text) - 1))
Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
End Select
Next

End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

I am merging several different excel spreadsheets and some of the names

are in all uppercase and some are first initial of name in uppercase and the
rest in lowercase. Is there an easy way (as in Word) to highlight all the
names in uppercase and automatically change them to first letter uppercase,
rest lower case???

 

[Bob Flanagan]

You can use the function Proper(cell ref) to get part way there. It works
great on names like john q doe. However, on names like ron mcdonald, which
comes out Ron Mcdonald, not Ron McDonald. You can then do a series of
global changes to fix these.

If you have to do the task often, you can automate it with the Data Cleaner
available from my site. It allows you to store such global change commands
and use them over and over.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

I am merging several different excel spreadsheets and some of the names

are in all uppercase and some are first initial of name in uppercase and the
rest in lowercase. Is there an easy way (as in Word) to highlight all the
names in uppercase and automatically change them to first letter uppercase,
rest lower case???

 

[Gord Dibben]

Susan

Sub Proper_Case()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = Application.Proper(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Select the columns or ranges to alter then run the macro.

For help on getting started with macros and VBA code see David McRitchie's
site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP

 

[Paul Ades]

Just a few comments on changing text to proper case in Excel. You can use
Excel's =PROPER() function (either directly in a formula on a worksheet or as
Application[.WorksheetFunction].Proper in VBA), or you can use VBA's
StrConv(Text,vbProperCase). Running under VBA, StrConv is about 4 times faster
than Application.Proper. However, neither function is without its warts.

As Bob Flanagan points out, Mcdonalds doesn't fair well with either function.

Further, VBA's StrConv doesn't recognize the letter following an apostrophe as
ever needing capitalization, so StrConv("o'sullivan", vbProperCase) returns
"O'sullivan." Excel's =PROPER(), on the other hand, capitalizes EVERY letter
which follows an apostrophe. This goes too far and doesn't account for the use
of 's for contractions or as a possessive. So while =PROPER(o'sullivan)
correctly returns O'Sullivan, =PROPER("st. luke's cathedral") returns "St.
Luke'S Cathedral."

Additionally, =PROPER also capitalizes the first letter following a leading
numeral, so =PROPER(32nd st.) returns 32Nd St. -- StrConv ignores the issue and
treats the 3 as the first letter, not the n.

I guess yer pays yer money and takes yer choice. Or write exception handlers
with VBA, as I've had to do on a few occasions. Or did MS fix any of this in
Excel 2003?

Best Regards,
Paul Ades