Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

excel text column reformatting

Options
  • 09-05-2007 12:30pm
    #1
    lunacyfoundme
    Registered Users Posts: 462 ✭✭


    Just a wee query about text conversion or reformatting in excel. Im pulling information from an oracle database into an excel document and all the text like FirstName LastName etc is in all caps by default. Is there anyway to reformat this in excel so that the first letter in each field is caps and the rest in smalls, for example JOHNNY COOLKID becomes Johnny Coolkid.


Comments

  • Options
    #2
    Eoin
    Registered Users Posts: 21,257 ✭✭✭✭Eoin


    lunacyfoundme wrote:
    Just a wee query about text conversion or reformatting in excel. Im pulling information from an oracle database into an excel document and all the text like FirstName LastName etc is in all caps by default. Is there anyway to reformat this in excel so that the first letter in each field is caps and the rest in smalls, for example JOHNNY COOLKID becomes Johnny Coolkid.

    Do you have access to edit the query?

    If so, try this:
    select nls_initcap(COLUMN_NAME) from TABLE_NAME

    Failing that, there seem to be a few add-ins you can try for Excel.

    e.g.
    http://www.digdb.com/excel_add_ins/convert_upper_lower_proper_case/


  • Options
    #3
    Squall
    Registered Users Posts: 1,345 ✭✭✭Squall


    You could try creating a Macro to convert it all for you.

    Is there VBA code pulling in the data? You could try manipulating that to change the data there


  • Options
    #4
    cyberbob
    Closed Accounts Posts: 82 ✭✭cyberbob


    you could just use the Proper() function in excel :rolleyes:


  • Options
    #5
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil


    cyberbob wrote:
    you could just use the Proper() function in excel :rolleyes:

    Any suggestions an how he's going to execute the proper() function other than manually? Unhelpful sarcastic posts could get you banned from this forum.


  • Options
    #6
    Heinrich
    Closed Accounts Posts: 1,577 ✭✭✭Heinrich


    Evil Phil wrote:
    Any suggestions an how he's going to execute the proper() function other than manually? Unhelpful sarcastic posts could get you banned from this forum.

    He could write a simple macro.
    It depends on waht exactly he needs.


  • Advertisement
  • Options
    #7
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil


    Heinrich wrote:
    He could write a simple macro.
    It depends on waht exactly he needs.

    Yes, I know. I was wearing my moderators hat in that post.


    lunacyfoundme: How exactly are you populating the spreadsheet with the oracle data?


  • Options
    #8
    Heinrich
    Closed Accounts Posts: 1,577 ✭✭✭Heinrich


    Evil Phil wrote:
    Yes, I know. I was wearing my moderators hat in that post.


    lunacyfoundme: How exactly are you populating the spreadsheet with the oracle data?


    a quickie

    Sub Macro1()

    ActiveCell.FormulaR1C1 = "=PROPER(RC[-3])"
    Selection.AutoFill Destination:=Range("D1:D32"), Type:=xlFillDefault
    Range("D1:D32").Select
    End Sub


Advertisement