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
Solved

Change all uppercase words to capitalize the first letter only.

Posted on 2016-07-29
18
80 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
  • 7
  • 6
  • 2
  • +2
18 Comments
 
LVL 50

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:itjockey
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:itjockey
ID: 41734843
opps sorry i was attaching file..
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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 30

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 18

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 30

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 18

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 30

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 30

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 30

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 30

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 30

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

860 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