Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Marlborough, Massachusetts, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Test format from All Caps to initial caps (O2k SR-1)

    I occasionally get lists of contact names addresses etc, in all caps. I am trying to convert to initial caps. Back and forth to Word does not seem to help me figure it out. Any suggestion?.
    Thanks

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    the following will convert all cells in the selected range:

    Sub ConvertToUpperCase()
    Dim rng As range
    For Each rng In Selection.Cells
    If rng.HasFormula = False Then
    rng.Value = UCase(rng.Value)
    End If
    Next rng
    End Sub
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Sub ConvertToLowerCase()
    Dim rng As range
    For Each rng In Selection.Cells
    If rng.HasFormula = False Then
    rng.Value = LCase(rng.Value)
    End If
    Next rng
    End Sub
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Sub ConvertToProperCase()
    Dim rng As range
    For Each rng In Selection.Cells
    If rng.HasFormula = False Then
    rng.Value = StrConv(rng.Value, vbProperCase)
    End If
    Next rng
    End Sub


    HTH

    Brooke

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    As well as Brooke's VBA routines there are worksheet functions to do much the same. = Proper(A1) will convert text in A1 to initial caps, or what is known as Proper Case. There are also the UPPER() and LOWER() functions.

    Andrew

  4. #4
    Star Lounger
    Join Date
    Apr 2001
    Location
    Marlborough, Massachusetts, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    Thanks for the response, but...I have no idea what to do with that answer, and would be afraid to do a reg edit. Can you suggest a way to do it with a Joey-the-dunce user method.
    tTanks

  5. #5
    Star Lounger
    Join Date
    Apr 2001
    Location
    Marlborough, Massachusetts, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    Sorry for being thick. I tried f proper and got no where. I am trying to do a colum with 150 company names in. sorry to be slow but, more directions would be greatly appreciated.
    regards,

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    You should have no problems with then PROPER() function. If your list of Company names starts in Column A1, then in B1 enter =PROPER(A1) and copy it down to B150 or whatever. That should give you a list of company names, with the initial letter of each word capitalised, (unless theer is a space between each letter of your current data).

    If that does not work, please explain what result or error message you get.

    Andrew C

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    There is an excellent utility (ASAP Utilities) available from <A target="_blank" HREF=http://www.asap-utilities.com/>http://www.asap-utilities.com/</A> that has the function you require. (and many other useful tricks).

    After installing the addin - (instructions are provided ont he website).
    Look under the Text heading. The option you want is Start Each Word With Uppercase.


    HTH

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    Just in case you haven't used the built in functions that Andrew mentions and still want that walk through, open the workbook that you will be using the function in and press [Alt] + [F11]. This should bring up the VBA Editor window. On the left side of this window the project explorer should be visible (a window that shows the open workbooks and all of the objects in them - If this is not visible, then select "Project Explorer" from the View menu.)

    In the Project Explorer find the workbook that you opened and select it, on the insert menu select module and then paste the code I posted into this module. Now, when you return to excel, the functions should be visible in the paste function wizard listed in either in the "all functions" or "user defined functions" categories.

    If you need any more help, just ask.

    Brooke

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    Just to add a little to what Andrew replied: What he did will give you a column with the names in Proper case (Initial capital on each word). However, you will still have the column with all Caps and you can't delete it since the formulas will then give an error instead of the names. After doing what Andrew told you, you can then select the cells with the proper case and select copy from the Edit menu. Then select "Paste Special " from the Edit menu. In the resulting dialog box, in the "Paste" section click on "Values" then Click the OK button. This will replace the formulas with the text. You can now delete the original column with the all Caps names.
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    Andrew and Legare are quite correct as usual. But Brooke's macro (I've used the same one for ages) is really the best solution in this case, just select the list, run the macro, and the job is done. No writing formulas, filling down, copying, paste specials, etc.

  11. #11
    Star Lounger
    Join Date
    Feb 2001
    Location
    Glasgow, Lanarkshire, Scotland
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    I too am struggling to use VB in Excel 2000 SR-1. I have followed this thread as I was having difficulty accessing user defined functions I had written.

    I followed your advice and all is well except that I cannot access the routines from Insert|Function - they are not visible under "all functions" and there is no "user defined" category. If I open up Tools|Macro|Macros, the routines are there and can be run from there. If I define a function as a Module for the workbook in the VB editor, it *does* appear as a "user defined" function as you suggest.

    Can you throw any light?

    Thanks.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    Hi John,
    A Sub is a procedure, not a function which is why it does not appear in your functions list. You wouldn't use this procedure in a cell (i.e. you wouldn't type "=ConvertCaps()" or whatever), you would simply run it and it would work on the relevant cells.
    Broadly speaking, functions return values whereas procedures don't, they simply perform a set of instructions.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Star Lounger
    Join Date
    Feb 2001
    Location
    Glasgow, Lanarkshire, Scotland
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    This sounds logical. I would say in my own defence that I was merely querying what Brooke had said in an earlier reply in this thread - honest!

    Would one normally run a procedure either from the Macros screen or via a CTRL+key combo or is there another route?

    Thanks for your help.

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    John,
    It's a matter partly of preference and partly of how often you use a given procedure. Any procedures that I use frequently, I assign to toolbar buttons (the toolbars get crowded very quickly though, even if you use the right-click shortcut menus as well!), others I run from the Macro menu. I very rarely use shortcut keys as I can't remember them if I don't use a procedure for a while, and Excel already uses a lot of the combinations.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Test format from All Caps to initial caps (O2k SR-1)

    Rory
    What do you mean by the right-click shortcut menus?

    I agree that the toolbars get very crowded, and going through Tools/Macros gets tedious, following a suggestion by Legare I put my frequently used macros on a custom tool bar which is quicker than Tools/Macros and takes much less room than many buttons.

Page 1 of 2 12 LastLast

Posting Permissions

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