How do I capitalize the first letter of multiple cells? in Excel General Questions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
dominic_howden 9/21/2006 3:44 AM PST
  Question
  I'm trying to clean a mailing list (1000+ rows), but the capitalization of
the cells containing name, salutation etcetera is variable. How can I ensure
that the contents of all cells start with a capital letter?
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Otto Moehrbach 9/21/2006 4:14 AM PST
  Answer
  Let's say that your names are in Column A starting with A2.
Find a blank column to the right of all your data, say Column H.
In H2, type "=Proper(A2)" without the quotes.
Drag that formula down as far as the names in Column A go.
Click Edit - Copy.
Select A2.
Click Edit - Paste Special - Values.
Click OK.
Delete Column H.
Done.
HTH Otto
"dominic_howden" <dominichowden@discussions.microsoft.com> wrote in message
news:3786B90D-7922-4E07-A3FA-A72BE11F0A2F@microsoft.com...
> I'm trying to clean a mailing list (1000+ rows), but the capitalization of
> the cells containing name, salutation etcetera is variable. How can I
> ensure
> that the contents of all cells start with a capital letter?


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
dominic_howden 9/21/2006 4:54 AM PST
   
  Otto - that is superb - you've just saved me about 2 days of work! Not a
function I've come across, but very easy to apply.

- Dominic

"Otto Moehrbach" wrote:

> Let's say that your names are in Column A starting with A2.
> Find a blank column to the right of all your data, say Column H.
> In H2, type "=Proper(A2)" without the quotes.
> Drag that formula down as far as the names in Column A go.
> Click Edit - Copy.
> Select A2.
> Click Edit - Paste Special - Values.
> Click OK.
> Delete Column H.
> Done.
> HTH Otto
> "dominic_howden" <dominichowden@discussions.microsoft.com> wrote in message
> news:3786B90D-7922-4E07-A3FA-A72BE11F0A2F@microsoft.com...
> > I'm trying to clean a mailing list (1000+ rows), but the capitalization of
> > the cells containing name, salutation etcetera is variable. How can I
> > ensure
> > that the contents of all cells start with a capital letter?
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Calloway Jim 8/7/2007 12:16 PM PST
   
  Otto,

Thank you soooooo much. This was great.

Jim

"Otto Moehrbach" wrote:

> Let's say that your names are in Column A starting with A2.
> Find a blank column to the right of all your data, say Column H.
> In H2, type "=Proper(A2)" without the quotes.
> Drag that formula down as far as the names in Column A go.
> Click Edit - Copy.
> Select A2.
> Click Edit - Paste Special - Values.
> Click OK.
> Delete Column H.
> Done.
> HTH Otto
> "dominic_howden" <dominichowden@discussions.microsoft.com> wrote in message
> news:3786B90D-7922-4E07-A3FA-A72BE11F0A2F@microsoft.com...
> > I'm trying to clean a mailing list (1000+ rows), but the capitalization of
> > the cells containing name, salutation etcetera is variable. How can I
> > ensure
> > that the contents of all cells start with a capital letter?
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Adam Birken 12/11/2008 12:44 PM PST
   
  Otto,

You ROCK!!!!!!!!!!!!!!!!!

"Otto Moehrbach" wrote:

> Let's say that your names are in Column A starting with A2.
> Find a blank column to the right of all your data, say Column H.
> In H2, type "=Proper(A2)" without the quotes.
> Drag that formula down as far as the names in Column A go.
> Click Edit - Copy.
> Select A2.
> Click Edit - Paste Special - Values.
> Click OK.
> Delete Column H.
> Done.
> HTH Otto
> "dominic_howden" <dominichowden@discussions.microsoft.com> wrote in message
> news:3786B90D-7922-4E07-A3FA-A72BE11F0A2F@microsoft.com...
> > I'm trying to clean a mailing list (1000+ rows), but the capitalization of
> > the cells containing name, salutation etcetera is variable. How can I
> > ensure
> > that the contents of all cells start with a capital letter?
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
PlayingToAudienceOfOne 6/5/2007 6:25 AM PST
   
  Sub Change_Case()
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

"dominic_howden" wrote:

> I'm trying to clean a mailing list (1000+ rows), but the capitalization of
> the cells containing name, salutation etcetera is variable. How can I ensure
> that the contents of all cells start with a capital letter?
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies