Need help in formatting Excel files

  • Need help in formatting Excel files

    I had posted the following help message in the wrong section. Sorry about that.

    Can someone please help me to do the following in my Excel files. I have created a number of Excel files from the original files by deleting some columns that are not needed in the file. I find that the text in the newly created files (rows 5 and 6) are in upper case like the original files. I do want to change the text into proper case. For example, I want to convert “UNIVERSITY OF MARYLAND” into “University of Maryland”. I know how to do this column by column (there are 8 columns in my data file). But I want to write a macro so that I can do the formatting into Proper case globally. Is it possible to highlight the row and make the change I am looking for only once?Thank you very much.Angela

  • You don't really need a macro (see attached)....

    If you really wanted to use a macro you could also read the entire area into an array (the following is designed to point you in the right direction ...not proper code)

    Select a cell in the area (Range("A1").select

    Apply a named range to the current region
    Names.Add Name:="Source", RefersTo:=ActiveCell.CurrentRegion

    Read the Source Area into a (VBA) Array
    TempData() = Range("Source").Value

    Loop through values in Array converting to proper as you go then save array back to Named Range:=
    Range("Source").value = TempData()

    Robert Hind
    Perth - Western Australia

  • Hi r-hind,

    Thank you very much for getting back to me with an appropriate solution to my formatting question in Excel. I have about 400 or more files to do the formatting this way and I would like a macro written for this purpose. Is it possible for you to write a macro for this purpose? I will try also to write the macro. I am very thankful for your kindly help and solution. I have a question regarding downloading of the attachment in your message. I find that I had downloaded a file called “source.xls”. Is that the correct file?


    Hi AJW,

    Thank you very much for getting back to me with information on an add-in developed by you. I would very much like to use to improve my workbook appearance. However, when I tried to download “My Controls.xla” I did not really succeed. Please let me know the correct way to download the file. Again, thanks a lot for getting back to me.


  • Angela

    Ok ..I've got a few busy days coming up will try to help as much as I can and I know others who frequent this forum will be helpful (and quicker).

    If I'd realised you had 400 files my previous reply would have been different.

    Some more questions:-

    Are all the files in the same format (same number of columns and same type of content)?

    Do all the columns contain data that needs to be converted?
    Is there only one worksheet per file or are there multiple worksheets?

    Are all the files (or can all the files be) stored in one folder? What is the full name of the folder?

    Do you have a sample file or files that you can post here?

    The file you downloaded (at least by name) is not the correct file. I usually find the best way to download is right click on the link and select "Save Target as" or "save file as".

    Robert Hind
    Perth - Western Australia

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!