Solved

Change all uppercase words to capitalize the first letter only.

Posted on 2016-07-29
18
91 Views
Last Modified: 2016-08-04
I have 39 columns and 13,282 rows in a spreadsheet.

I need to prompt for column selection and change all uppercase words in the selected column to capitalize only the first letter of each word.
0
Comment
Question by:frugalmule
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +2
18 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41734839
Hi,

pls try

Sub Macro()

Set Rng = Application.InputBox("Select Column", "Column selection", , , , , , 8)
    For Each c In Rng.SpecialCells(xlCellTypeConstants, 2)
        c.Value = Application.Proper(c.Value)
    Next
End Sub

Open in new window

Regards
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 41734841
Step 1 Copy data and past to attached Workbook - Sheet Actual file
Step 2 press ctrl+r
Done


Thanks
Proper.xlsm
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 41734843
opps sorry i was attaching file..
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:frugalmule
ID: 41734851
I don't need the actual file, just the code.  When I run it on the sheet in question, then input column AC, it tells me type mismatch.
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41734887
You may try something like this...
The code below will prompt you to enter the column letter and it will convert all the strings in that column into proper strings.
Sub ConvertColumnToHaveProperStrings()
Dim colStr As String
Dim colNum As Long
Dim cell As Range
colStr = Application.InputBox("Input Column Letter...", "Column Letter Please!")
On Error Resume Next
colNum = Range(colStr & 1).Column
If colNum = 0 Then
   MsgBox "You have entered invalid column letter...", vbCritical, "Invalid Column Letter!"
   Exit Sub
End If
For Each cell In ActiveSheet.Columns(colNum).SpecialCells(xlCellTypeConstants, 2)
   cell.Value = StrConv(cell.Value, 3)
Next cell
MsgBox "Task Completed.", vbInformation, "Done!"
End Sub

Open in new window

0
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41734890
No need for a Loop

Sub test()
    Dim rng As Range
    Set rng = Application.InputBox("Select Column", "Column selection", , , , , , 8)
    rng.SpecialCells(xlCellTypeConstants, 2).Value = Application.Proper(rng.Value)

End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41734894
That's correct Roy! Thanks for pointing this out. :)

In this case the code can be written as below.....

Sub ConvertColumnToHaveProperStrings()
Dim colStr As String
Dim colNum As Long
Dim cell As Range
colStr = Application.InputBox("Input Column Letter...", "Column Letter Please!")
On Error Resume Next
colNum = Range(colStr & 1).Column
If colNum = 0 Then
   MsgBox "You have entered invalid column letter...", vbCritical, "Invalid Column Letter!"
   Exit Sub
End If
ActiveSheet.Columns(colNum).SpecialCells(xlCellTypeConstants, 2).Value = StrConv(ActiveSheet.Columns(colNum).SpecialCells(xlCellTypeConstants, 2), 3)
MsgBox "Task Completed.", vbInformation, "Done!"
End Sub

Open in new window

0
 

Author Comment

by:frugalmule
ID: 41734901
Hi Roy,

I like the idea of not having a loop but I still get type mismatch
0
 

Author Comment

by:frugalmule
ID: 41734932
I also tested bottom solution from Subodh, and it allowed me to input a column letter but does not appear to have done anything.  I'm not 100% sure whether it just missed some or didn't get any of them.
0
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41734937
Attach an example of the data. My test workbook runs fine and  will be faster than a Loop
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41734957
Okay try the older version in the attached and let me know if that works for you.
Click the button on the sheet to run the code on Module1.
StringsToProperCase.xlsm
0
 

Author Comment

by:frugalmule
ID: 41734959
Hi Roy,

I'll work on the example.  This is a video of the error.
http://screencast.com/t/yKBIBRDyMgPw
0
 

Author Comment

by:frugalmule
ID: 41734965
Roy here is an sample that produces the error along with a video of the error.
http://screencast.com/t/yKBIBRDyMgPw
eesample.xlsb
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41734979
Please find the attached. I tested the code on Col. B and it worked without an issue.
eesample.xlsb
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41734983
If you want to run that code on any column, just input the column letter only.
e.g. if you want to run that code on column M, then input letter M in the inputbox.
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41735000
Here is the a short demo...

http://www.screencast.com/t/cHqLeMcLryI4
0
 

Author Comment

by:frugalmule
ID: 41735008
Yours works but is VERY VERY slow.  Hoping to test code from Roy and have requested a re-open.
0
 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41735025
Okay replace my existing code with the following one. It doesn't involve loop.
Sub ConvertColumnToHaveProperStrings()
Dim colStr As String
Dim rng As Range
colStr = Application.InputBox("Input Column Letter...", "Column Letter Please!")
On Error Resume Next
colNum = Range(colStr & 1).Column
If colNum = 0 Then
   MsgBox "You have entered invalid column letter...", vbCritical, "Invalid Column Letter!"
   Exit Sub
End If
Set rng = Range(colStr & ":" & colStr)
Range(colStr & ":" & colStr).SpecialCells(xlCellTypeConstants, 2).Value = Application.Proper(rng.SpecialCells(xlCellTypeConstants, 2).Value)

MsgBox "Task Completed.", vbInformation, "Done!"
End Sub

Open in new window


OR you can replace the Roy's code with the following code...
Sub test()
Dim rng As Range
Set rng = Application.InputBox("Select Column", "Column selection", , , , , , 8)
rng.SpecialCells(xlCellTypeConstants, 2).Value = Application.Proper(rng.SpecialCells(xlCellTypeConstants, 2).Value)
End Sub

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question