Excel change case

[jjatcal]

Hello,

I have a spreadsheet given to me from a colleague. Unfortunately all the information in it is capitalized. There are many columsn which contain text AND THEY ARE ALL CAPITALIZED.

Before I distribute it out to the team, I want to make it a little easier on the eyes. But I can't seem to find how to change case on Excel. I tried opening the Excel document through Word to use Word's change case feature. But lo and behold, Word did not maintain the tab delimited structure, so not helpful.

How do I change case in Excel?

Thanks.

 

[kphu]

=proper(celladdress)

 

[kphu]

you can then copy and paste special. values only

 

[kphu]

your other option is =lower(text) and it'll convert all the text to lower case.

hope that helps.

 

[jjatcal]

Where do I put this code? Please be specific as I have limited knowledge of it.

Thanks.

 

[KenWright]

First and foremost - DONT open Excel files in Word - they don't like it.

Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you need to search for
your workbook's name, and when you find it you may need to click on the + to expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook


Right click on the VBAProject(Your_Filename) bit and choose Insert / Module.

You should now see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

In the white space that just opened up, paste the following code:-

Sub ProperCase()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim c As Range
For Each c In ActiveSheet.UsedRange
If c.HasFormula = False Then
c.Value = StrConv(c.Value, vbProperCase)
End If
Next c
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Then hit File / Close and return to Microsoft Excel and save the file.

Now hit Tools / Macro / Macros and select ProperCase and it should fix all the data.



If you now want to get rid of the macro, then follow the next steps:-


Hit ALT+F11 and this will open the VBE (Visual Basic Editor)
Top left you will hopefully see an explorer style pane. Within this pane you need to search for
your workbook's name, and when you find it you may need to click on the + to expand it. Within
that you should see the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1

If you have named your sheets then those names will appear in the brackets above as opposed to
what you see at the moment in my note.

Right click on the module and select remove. When prompted with a question re exporting, just
hit no. Then hit File / Close and return to Microsoft Excel and save the file.

Regards
Ken...............

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[KenWright]

Should have said - ALWAYS back up your data first if you are doing something you are not sure about.

Regards
Ken..............

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------