Solved

Change all uppercase words to capitalize the first letter only.

Posted on 2016-07-29
18
74 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 49

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
 

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 28

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 17

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 28

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 17

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 28

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 28

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 28

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 28

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 28

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now