Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 10th 06, 06:16 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 921
Default Default Capital letter for 1st letter of a word

Hi, I am trying to auto format excel entry to default capital letter for 1st
letter of each word. For example: When user insert '10, international
building, sunny road', the entry will updated to '10, International Building,
Sunny Road'.

Please show me the method.

Your help is very much appreciated.

Jeff.


  #2   Report Post  
Old July 10th 06, 06:25 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 718
Default Default Capital letter for 1st letter of a word

=PROPER(A1)

HTH
--
AP

"Jeff" a écrit dans le message de news:
...
Hi, I am trying to auto format excel entry to default capital letter for
1st
letter of each word. For example: When user insert '10, international
building, sunny road', the entry will updated to '10, International
Building,
Sunny Road'.

Please show me the method.

Your help is very much appreciated.

Jeff.



  #3   Report Post  
Old July 10th 06, 07:23 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 921
Default Default Capital letter for 1st letter of a word

Hi Ardus

Thanks for you reply. Your recommended method is to use formula =PROPER(A1).
It's referring to another cell, update to correct format and appear in
existing cell. Is there a way to update to correct format immediately in
existing cell withot referring to another field ? Some kind of excel
validation function.

Jeff.

"Ardus Petus" wrote:

=PROPER(A1)

HTH
--
AP

"Jeff" a écrit dans le message de news:
...
Hi, I am trying to auto format excel entry to default capital letter for
1st
letter of each word. For example: When user insert '10, international
building, sunny road', the entry will updated to '10, International
Building,
Sunny Road'.

Please show me the method.

Your help is very much appreciated.

Jeff.




  #5   Report Post  
Old July 10th 06, 07:52 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 921
Default Default Capital letter for 1st letter of a word

Ardus,

Thanks. I will suggest to Microsoft to include this as one of excel
validation option.

Jeff.

"Ardus Petus" wrote:

AFAIK, this does not exist.
But you can copy/Paste special values from calculated column back into
original column.

Cheers,
--
AP

"Jeff" a écrit dans le message de news:
...
Hi Ardus

Thanks for you reply. Your recommended method is to use formula
=PROPER(A1).
It's referring to another cell, update to correct format and appear in
existing cell. Is there a way to update to correct format immediately in
existing cell withot referring to another field ? Some kind of excel
validation function.

Jeff.

"Ardus Petus" wrote:

=PROPER(A1)

HTH
--
AP

"Jeff" a écrit dans le message de news:
...
Hi, I am trying to auto format excel entry to default capital letter
for
1st
letter of each word. For example: When user insert '10, international
building, sunny road', the entry will updated to '10, International
Building,
Sunny Road'.

Please show me the method.

Your help is very much appreciated.

Jeff.









  #7   Report Post  
Old July 10th 06, 08:36 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 921
Default Default Capital letter for 1st letter of a word

Ardus

I got reply from Excel Expert Bob Philips, the solution is as follow

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Not IsNumeric(.Value) Then
If Not .HasFormula Then
.Value = Application.Proper(.Value)
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

================================================== ==

"Ardus Petus" wrote:

You could use a Worksheet_Change event procedure to "properize" entries in
certain cells.

--
AP

"Jeff" a écrit dans le message de news:
...
Ardus,

Thanks. I will suggest to Microsoft to include this as one of excel
validation option.

Jeff.

"Ardus Petus" wrote:

AFAIK, this does not exist.
But you can copy/Paste special values from calculated column back into
original column.

Cheers,
--
AP

"Jeff" a écrit dans le message de news:
...
Hi Ardus

Thanks for you reply. Your recommended method is to use formula
=PROPER(A1).
It's referring to another cell, update to correct format and appear in
existing cell. Is there a way to update to correct format immediately
in
existing cell withot referring to another field ? Some kind of excel
validation function.

Jeff.

"Ardus Petus" wrote:

=PROPER(A1)

HTH
--
AP

"Jeff" a écrit dans le message de
news:
...
Hi, I am trying to auto format excel entry to default capital letter
for
1st
letter of each word. For example: When user insert '10,
international
building, sunny road', the entry will updated to '10, International
Building,
Sunny Road'.

Please show me the method.

Your help is very much appreciated.

Jeff.












Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lotus notes link Yogi Smith Excel Discussion (Misc queries) 1 July 10th 06 04:59 AM
can i select from list by typing first letter of word? LISAWATKIN Excel Discussion (Misc queries) 1 May 13th 05 09:31 PM
I want to type the letter Y in a cell and have the word Yes appea. jhardw New Users to Excel 3 April 2nd 05 12:24 AM
change the default word in the insert comment option in excel excel Setting up and Configuration of Excel 1 December 28th 04 02:50 PM
change the default word in the insert comment option in excel excel Setting up and Configuration of Excel 2 December 24th 04 12:51 PM


All times are GMT +1. The time now is 12:33 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright ©2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017