Make Text Proper / Sentence Case In VBA Module
Jul 29, 2003Some handy code that I can put in a VBA module that will convert all text within a Spreadsheet to Proper or Sentence like this ---> Hello Everyone, Hope You Are All Happy.
View 7 Replies
ADVERTISEMENT
Proper Case/Sentence Case In Macro Code
May 8, 2008Sub Addy()
Do Until ActiveCell. Offset(0, -4) = ""
Renamer = Proper(ActiveCell)
ActiveCell = Renamer
ActiveCell.Offset(1, 0).Select
Loop
End Sub
fail? Trying to remove all capitals from names/addresses. Error message is "compile error - sub or function not defined"
Proper Case Text By Condition
Oct 19, 2007How can I extend proper() to NOT change "PO Box 333" to "Po Box 333". Ideally, I would like to supply a list of words such as PO and all the 2 letter directionals (NE,NW,SE,SW).
There are also cases such as a last name of MacNamara which should have a capital M and N. Even worse, I see that 3rd becomes 3Rd which is very sad.
I'm assuming the data was supplied in uppercase
Change Case Of Text To Proper Except For Prepositions
Oct 17, 2012macro that will change the case of a string of texts to proper except for prepositions (e.g. and, or, about, the, etc.).
View 2 Replies View RelatedChange Text To Upper, Lower Or Proper Case On Entry
Sep 6, 2007I would like to format a row of cells so that when a word is entered into the cell it automatically becomes a capital.
I need the word to be capitalized so that I can use it in a custom function. The function uses the word from this cell and goes through a bunch of cases in determing how to classify the string.
I think more than one solution is possible and I would greatly appreciate some feed back, I've tried looking into turning all the letters of a string in my VBA code to capitals, or a way to format the cells, so that the string is already capitalized when entered into the VBA code, but I'm still a novice at VBA and unsure on how certain commands work.
here is a sample of my vba code.
Function WeightI(Shape As String, sDim As String, dLenFt As Double) As Double
Const pi As Double = 3.14159265358979
Const Ft2In As Double = 12
Const dDen As Double = 0.2835 ' density of steel, pounds per cubic inch
Dim aiStr() As String ' dimensions as strings
Sentence Case FORMULA?
Mar 12, 2009I know the PROPER function will convert all text to capitalise each word, is there a formula that can convert only the first letter to caps and the rest to lower case?
View 4 Replies View RelatedSentence Case In Cells For Ranges
Apr 16, 2014How do I enforce for ranges A1:A10 and C1:C10 that whatever is entered in these cells is changed to sentence case, i.e. "today it is Raining." will change to "Today it is raining.".
I thought of having helper columns with the following formula that would then paste over the ranges on a Workbook.close event but it seems long-winded and not the right way of doing it.
Formula for helper columns:
[Code] ......
VBA Convert To Proper Case
Jun 30, 2013I've had good success with the following line of code
Code:
Sheets("Test").Range("A2") = UCase(Sheets("Test").Range("A1"))
But how do I do the same, converting the to Proper Case?
Proper Case In A Macro
Feb 21, 2007I have just recently found that I can do case correction with Excel but I am manually having to do it how can I add it to my macro? The function for doing it does not seem straight forward to me on putting in macro I am sure it is simple but just missing some element of it.
I need to have Proper case for columns C, G and H from rows 11 and down.
Use Proper Case Only For Input Box
May 30, 2007how can i change the text put into an input box into proper case regardless of what my user puts in?
View 9 Replies View RelatedChange To Proper Case In VBA
Aug 10, 2009I found this bit of Worksheet_Change code to change the target area to UpperCase. This works fine.
If Not Intersect(Target, Columns(2)) Is Nothing Then
Set rng1 = Intersect(Target, Columns(2))
Set rng2 = Intersect(ActiveSheet.UsedRange, rng1)
For Each cell In rng2
If cell.Formula "" Then
cell.Formula = Format(cell.Formula, ">")
End If
Next cell
End If
I could not find anything telling me what the ">" means. I'm assuming that it is a special symbol/wildcard for UCase in VBA.
My question(s), is there a symbol for ProperCase so I can use the same code, just making it change the Target column to Proper? Also is there a list of the special symbols.
Proper Case Exemptions
Jun 3, 2006When I asked this question before, I was looking for a way to automate the exemptions on a UserForm. At that time I realized that automation was not a good choice and went with a CommandButton to turn off the Proper case for that entry. I am now trying to do the same thing on a Worksheet change event using this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column < 1 Or Target.Column > 9 Then Exit Sub
On Error Goto Errhndl
Application.EnableEvents = False
If Target.Column = 1 Then Target.Value = Application.Proper(Target.Value)
If Target.Column = 2 Then Target.Value = Application.Proper(Target.Value)
If Target.Column = 3 Then Target.Value = UCase(Target.Value)
If Target.Column = 4 And Target.Value > "" Then Target.Value = UCase(Left(Target.Value, 2)) & "-" & Right(Target.Value, 2)
If Target.Column = 8 Then Target.Value = UCase(Target.Value)
If Target.Column = 9 Then Target.Value = UCase(Target.Value)
Application.EnableEvents = True
Exit Sub
Errhndl:
Application.EnableEvents = True
End Sub
My problem is with Target.Column = 1. I need a way to disable the proper case for a single row. I tried to use an additional column (J) and place a x in that row, but I could not figure out how to detect if there was anything in that column for the target row.
Force Correct Sentence Case In TextBox
Sep 21, 2007I want to force a UserForm TextBox to format the text typed in to have the first letter of each sentence capitalized and all other letters to be lower case when I exit the TextBox.
TextBox1.Text = Application.AutoCorrect.CorrectSentenceCap = True
But when I used it with the TextBox Exit event, it deleted the text I had typed in and replaces it with the word "True".
Is there any other way to format text to capitalize only the first letter of each sentence?
Just realized that I might not need the "TextBox.Text =" so I removed it. The text was not deleted, but it was not capitalized either.
Extract Proper Case Words
Aug 17, 2014I have been using following code to extract all upper case words in a string but the problem is I can not extract words which are proper. For example
This is GOOD
Present output: GOOD
Desired Output: This GOOD
[Code] ....
What can be suitable modification in this case?
Converting Worksheet To Proper-Case
Sep 9, 2009I have an Excel 2003 worksheet with all the data in it in Upper-case.
I found this VBA code which works if you change a cell.
Proper Case Mac, Mc Names In TextBox
Dec 10, 2008I have a number of textboxes into which I enter the surname of individuals ... at present the textboxes are set to store all names in Uppercase. Is there coding to return names beginning Mc... or Mac... ie McClOUD or MacDONALD, in the more recognized format. I am sure this has been included in the forum but could not find it in a search of the site.
View 4 Replies View RelatedProper Case On All Sheets Except Sheet1
Sep 19, 2006Is it possible to modify this code to exclude the first sheet in the workbook which is called Costs?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A14:A39
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A14:A39")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error Goto 0
End Sub
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A14:A39
''''''''''''''''''''''''''''''''''''''''''''
Refine Proper Case Macro
Jan 25, 2007I am using this macro to ensure that a range of cells appear in Proper Case. However I am encountering a drawback, sometimes I have text which I want in Upper Case but which is changed into Proper Case. I was wondering if there was a way to work around this. Example: Practical W/W appears as W/w or Woodturing (GMC) appears as Woodtrunign(gmc)
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to Proper case for the range A15:A40
''''''''''''''''''''''''''''''''''''''''''''
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A15:A40")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error Goto 0
End Sub
Prevent Proper Case Of Letters Following A Comma
Mar 17, 2007When using the PROPER function, it capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter, and converts all other letters to lowercase.
However, if A1 contains the text "2-cent's worth"; then =PROPER(A1) will return the following result: "2-Cent'S Worth".
Is there a way to prevent the PROPER function from capitalizing the first letter following the apostrophe?
Force Upper Or Proper Case On Entry
Feb 16, 2007Below is the existing code that I'm working with and would like to be able to make the ' name' column either Upper or Proper case on entry. I haven't decided which I'm going to use yet.
Set r = Sheet1.Range("A2:C65536")
If Not Intersect(Target, r) Is Nothing Then
sTgt = Trim(Target.Value)
If sTgt = "" Then Exit Sub
Select Case Target.Column
Case NmCol
If InStr(sTgt, ",") = 0 Then
iSpc = InStrRev(sTgt, " ")
Target.Value = Mid(sTgt, iSpc + 1) & ", " & Left(sTgt, iSpc - 1)
End If
Formatting Individual Columns To Proper And Upper Case
Mar 13, 2014I have a worksheet which is populated from a macro using the following code.
Code:
Sheets("Create Sub Contractor").Range("B6:B65").Copy
With Sheets("Sub Contractor Information").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.PasteSpecial (xlValues), Transpose:=True
End With
The problem is that some of what is being copied needs to be Proper and Some Upper, therefore I cant use a paste special option.
What I'd like to be able to do is format the columns in the destination sheet ("Sub Contractor Information") from row 4 down to what ever format they need to be individually as there are some columns that are numbers, some text and numbers........
Automatically Change To Proper Case Once The User Leaves Any Of The Referenced Cells
Nov 6, 2008In my worksheet there are ranges A3:C37, E3:E37, J3:K37 and P3:P37 that all contain text that I would like to automatically change to proper case once the user leaves any of the referenced cells.
I have tried various codes form this forum and searched for hours on the net for a solution to do this but no matter what I do/try nothing works (for long)
Another forum user did help me out with some code but there was an issue with column C, L & O (which are set as drop down lists) and when the code was put into the workbook these columns stopped working and froze the app.
How To Make Input Box Show In Specific Part Of A Sentence
Apr 26, 2012I have a paragraph that i am trying to change a number in the paragraph. example
John has 3 kids he sells one kid he has 2 kids left. Later John's wife figures out and kills John 4 times.
I am trying to replace the "4" to an imputed on an user fourm imput box can i do this?
Make 1st Letter Upper Case
Aug 8, 2007I have cells that have 1 or 2 words in them. Is there a formula I can use that will make the 1st letter of each word Upper Case. The whole range started out all caps. I used =lower(a1) to make them all lower. Now I just need 1st letter of each word upper.
View 9 Replies View RelatedMake Upper Case In Every Word In A Cell
Feb 12, 2009How can i convert into upper case every word in a given cell. For instance:
I have the following in column A: books pen desk
i want it to be like this: Books Pen Desk
VB Macro For Proper Case To Ignore "/" & "-"
Oct 7, 2008I have a Excel text document that I compile from multiple sources, and if they don't leave a "white space" before a "/", "-", or "(" the Proper Case command does not capitalize the word that immediately follows that symbol.
View 4 Replies View RelatedFind A Specific Sentence In A Cell Full Of Text
Apr 28, 2009Could you tell me how I can find a specific sentence within a cell that contains many sentences.
for example
I want to find, "I am new." within a text that contains, "Hello I am Bob. I am new. I live in england."
I am currently using =+FIND(AB$1,$V2) where AB1 contains the sentence I am looking for and V2 contains the cell full of sentences. However this returns #VALUE! when the sentence is not found. I want it to return null.
Convert Range Cell Of Text Into Proper Date
Jun 11, 2014I would like to know how can I convert a column P and T that contains these type of number into a proper way of date of DDMMMYYYY.
Like, if the cell in the column P has 8 digits then convert
19830425 -> 25APR1983
19910515 -> 15MAY1991
next cell till end of column P. Then do the same to column T.
Input Text From Group List If Lookup Found Certain Word In Sentence
Jan 27, 2012i have list in A2:A4 (description) and B2:B4 is the Group of.
now i want B2:B4 fill using E2:G2 (group list which is Animal, transportation and fruit) if one of the key word in E3:G5 found in A2:A4
A2: people like to eat apple
A3: car is very expensive
A4 : dog is human best friend
E2: Animal, F2 : transportation, G2: Fruit
E3:E5 = dog, cat, horse
F3:F5 = train, ship, car
G3:G5 = apple, banana, watermelon
result i want is :
B2 :Fruit,
B3 : transportation
and B4 is animal
Trying To Change A Text Box In Module
Dec 25, 2008I have a project with many forms and textboxes. I am trying to create a module that will add a "." (Chr45) in the value.
this is the code on the userform. the textbox name changes throughout the project
