Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    PROPER function (XP)

    I have a spreadsheet where the first column contains employees' last name and initials in UPPERCASE (example: SMITH DA). I want to convert this list to proper case, and I was delighted to discover the PROPER function. I'm having trouble with it, however. So far, I've use Excel Help to convert the first name in the list from "ADAM PA" to "Adam Pa." I thought perhaps I could just copy Cell A2 to all the others in the list, but that gave me 57 instances of "Adam Pa."

    So I have two obvious problems, one of which is that the middle initial ends up in lower case. What am I missing here?

    By the way, my next step will be to import this data into Access. Would I be smarter to just import this stuff as is and do the function thing in Access?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PROPER function (XP)

    If the names are in column A, enter formulas in column B, for example enter =Proper(A2) in cell B2. Then, fill down column B by double clicking the fill grip of cell B2. Next, select the cells in column B and drag them with the right mouse button to column A. When you release the mouse button, select Copy Here as Values Only from the popup menu. You can now delete the cells in column B.

    If you want to treat the middle initial correctly, you can use a formula such as

    =PROPER(LEFT(A2,FIND(" ",A2)))&MID(A2,FIND(" ",A2)+1,9999)

    the FIND function locates the space in the name; PROPER is applied to everything to the left of it; thr rest is left unchanged.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: PROPER function (XP)

    Hello, Hans--This stuff is pure magic! Thank you very much!

Posting Permissions

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