Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    England
    Posts
    26

    Unanswered: Apply A Formula to A Column

    Hello everyone

    Can anyone advise if this is possible?

    I have got several columns of name and address data which are in capitals. I want to apply the PROPER formula to get them all into scentence case so that it looks better for our customers.

    Is there a quick way that I can apply the formula by just clicking on the required column??

    Thanks,
    Elisabeth
    Beth

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: Apply A Formula to A Column

    If your Columns are filled then you can double click on the black box in the corner of the cell with the Formula in and that will fill down.
    The only other way i Lnow to fill the range would be to go to the Lastrow of your data and press <Ctrl> + <Shift> + <Up Arrow> and paste the formula in.

    if you want the formula in every cell of the column just Copy the Data, Highlight the Column you want then Paste. This will fill the Column

    Hope this Helps

    David

  3. #3
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Here's some code, by Ivan Moala, that will change text in the Used Range and give you options as to how to convert it. (Lower, Upper, Proper, Sentence).
    Code:
    Sub TextCon()
    'code by Ivan F. Moala
        Dim ocell As Range, 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
    Hope that helps,

    Smitty

Posting Permissions

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