"Proper" function

HenyLoc

New Member
Joined
Oct 23, 2002
Messages
3
I have a running list of names and would like to be able to enter the names without having to -shift- for the first letter of each name.

I found the "=proper" option but was unable to format whole columns. I was able to insert the code with a cell refrence then display the new text in a diffrent cell, but this would force me to have two list.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi HenryLoc:

Welcome to the Board!

After using the Proper function, you can paste the values back to the source data, then safely delete the additional column you had to use.

see the worksheet simulation
y021024U1.xls
ABCD
1nameProperHowtousethePROPERfunctionwithout
2henryHenryhavingtolivewithtwocolumns
3mrexcelMrExcelStep1
4jondoeJonDoeusethePROPERfunctionincolumnBtogetallthenamesincolumnAPROPEREd
5janesmithJaneSmithStep2
6bobBobhighlitecellsB2:B9thenEDIT|COPY,thendoan
7richRichEDIT|PASTE|SPECIAL_valuesovercellsA2:A9
8kenKenStep3
9normNormnowthecolumnBcanbesafelydeletedandthenamesinColumnAremainProper
Sheet4


Regards!

Yogi Anand
 

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hello:

There is a function that does that and a lot more on Asaputilities.....free down load...

pll
 

Paul B

Well-known Member
Joined
Feb 15, 2002
Messages
575
Right click on the worksheet tab and view code paste this code in it will change whats in column D to Proper case when you hit enter, change target column to the column you need
Private Sub Worksheet_Change(ByVal Target As Range)
'To change letters in column D to Proper
On Error GoTo Error_handler
With Target
If Not .HasFormula Then
Application.EnableEvents = False
If Target.Column = 4 Then Target.Value = Application.Proper(Target.Value)
Application.EnableEvents = True
End If
End With
Error_handler:
Resume Next
End Sub
 

HenyLoc

New Member
Joined
Oct 23, 2002
Messages
3
Hay Paul thanks that one worked but I need to do it on two columns. I pasted the code again and change the targets but it gave me

ambiguous name detected: Worksheet_Change

error

I need columns B & C to work

Thanks again- I heard about this site just 20 min ago and I have been looking for this answer for a year now.
 

HenyLoc

New Member
Joined
Oct 23, 2002
Messages
3
Nevermind I got it,
had to add a second line

If Target.Column = 2 Then Target.Value = Applicatin.Proper(Target.Value)
If Target.Column = 3 Then Target.Value = Applicatin.Proper(Target.Value)
 

Forum statistics

Threads
1,148,004
Messages
5,744,306
Members
423,859
Latest member
joannetung

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top