+ Reply to Thread
Results 1 to 7 of 7

Default Capital letter for 1st letter of a word

  1. #1
    Jeff
    Guest

    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. #2
    Ardus Petus
    Guest

    Re: Default Capital letter for 1st letter of a word

    =PROPER(A1)

    HTH
    --
    AP

    "Jeff" <[email protected]> a écrit dans le message de news:
    [email protected]...
    > 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. #3
    Jeff
    Guest

    Re: 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" <[email protected]> a écrit dans le message de news:
    > [email protected]...
    > > 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.
    > >

    >
    >
    >


  4. #4
    Ardus Petus
    Guest

    Re: Default Capital letter for 1st letter of a word

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

    Cheers,
    --
    AP

    "Jeff" <[email protected]> a écrit dans le message de news:
    [email protected]...
    > 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" <[email protected]> a écrit dans le message de news:
    >> [email protected]...
    >> > 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. #5
    Jeff
    Guest

    Re: 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" <[email protected]> a écrit dans le message de news:
    > [email protected]...
    > > 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" <[email protected]> a écrit dans le message de news:
    > >> [email protected]...
    > >> > 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.
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >


  6. #6
    Ardus Petus
    Guest

    Re: Default Capital letter for 1st letter of a word

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

    --
    AP

    "Jeff" <[email protected]> a écrit dans le message de news:
    [email protected]...
    > 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" <[email protected]> a écrit dans le message de news:
    >> [email protected]...
    >> > 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" <[email protected]> a écrit dans le message de
    >> >> news:
    >> >> [email protected]...
    >> >> > 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. #7
    Jeff
    Guest

    Re: 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" <[email protected]> a écrit dans le message de news:
    > [email protected]...
    > > 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" <[email protected]> a écrit dans le message de news:
    > >> [email protected]...
    > >> > 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" <[email protected]> a écrit dans le message de
    > >> >> news:
    > >> >> [email protected]...
    > >> >> > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1