Prevent Proper Case Of Letters Following A Comma

Mar 17, 2007

When 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?

View 9 Replies


ADVERTISEMENT

Proper Case/Sentence Case In Macro Code

May 8, 2008

Sub 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"

View 6 Replies View Related

Add Characters Between Lower Case And Upper Case Letters

Aug 26, 2009

I have a string of names that run together without spaces or commas between each name.

"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.

Is there a way to add a comma and space between a lower case and upper case letter?

View 7 Replies View Related

VBA Convert To Proper Case

Jun 30, 2013

I'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?

View 2 Replies View Related

Proper Case In A Macro

Feb 21, 2007

I 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.

View 9 Replies View Related

Use Proper Case Only For Input Box

May 30, 2007

how can i change the text put into an input box into proper case regardless of what my user puts in?

View 9 Replies View Related

Change To Proper Case In VBA

Aug 10, 2009

I 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.

View 9 Replies View Related

Proper Case Exemptions

Jun 3, 2006

When 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.

View 7 Replies View Related

Extract Proper Case Words

Aug 17, 2014

I 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?

View 8 Replies View Related

Converting Worksheet To Proper-Case

Sep 9, 2009

I 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.

View 6 Replies View Related

Proper Case Mac, Mc Names In TextBox

Dec 10, 2008

I 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 Related

Proper Case On All Sheets Except Sheet1

Sep 19, 2006

Is 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

''''''''''''''''''''''''''''''''''''''''''''

View 3 Replies View Related

Refine Proper Case Macro

Jan 25, 2007

I 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

View 2 Replies View Related

Proper Case Text By Condition

Oct 19, 2007

How 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

View 9 Replies View Related

Change Case Of Text To Proper Except For Prepositions

Oct 17, 2012

macro 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 Related

Force Upper Or Proper Case On Entry

Feb 16, 2007

Below 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

View 3 Replies View Related

Make Text Proper / Sentence Case In VBA Module

Jul 29, 2003

Some 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 View Related

Formatting Individual Columns To Proper And Upper Case

Mar 13, 2014

I 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........

View 4 Replies View Related

Change Text To Upper, Lower Or Proper Case On Entry

Sep 6, 2007

I 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

View 5 Replies View Related

Automatically Change To Proper Case Once The User Leaves Any Of The Referenced Cells

Nov 6, 2008

In 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.

View 4 Replies View Related

Insert Comma Between Letters And Numbers

Jan 30, 2014

I'm looking to insert a comma between letters and numbers like seen below using Windows 7 and Excel 2013. I'd prefer to do this through a formula and not a VBA script. I know this is easy to do if you know how many characters strings are going to be and the format stays consistent.

ABCD90bj10r7
ABCD,90,bj,10,r,7

ABCD90rt8r7
ABCD,90,rt,8,r,7

iFH15jr7ri12
iFH,15,jr,7,ri,12

iFG155jr8ri11
iFG,155,jr,8,ri,11

iFG15jr8ri9
iFG,15,jr,8,ri,9

View 2 Replies View Related

Calculating How Many Higher / Low Case Letters In A Row?

Jan 20, 2014

I have been developing a spreadsheet for my HR department to display sick leave and holidays amongst various other things. After completing it I was asked to tally up both half days and full days (Full days shown with a capital 'H' and half days with a lower case 'h')

I have tried various combinations of things to get this to work using Exact and if statements but had no luck.

View 4 Replies View Related

How To Get Cells To Only Have Upper Case Letters

Jul 30, 2014

Only have upper case letters.

I am trying to get certain cells to only have upper case letters.

each cell in the code will have a single letter typed in.

I would rather have it as an event code (change while it is typed), but I have not grasped the whole thing yet.

This code does not give me any errors, but it is not changing the lower case letters into upper case letters either.

[Code] ......

View 2 Replies View Related

VB Macro For Proper Case To Ignore "/" & "-"

Oct 7, 2008

I 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 Related

Select Case, Case Else Copy From Above Cell

Jun 3, 2009

I've got a pretty intense macro already written, a lot of Select Case components. At the end, if nothing matches I'd like to just copy the cell above to the cell below. However, there is a range of about 400 cells in length, so I'd need some sort of wildcard for range.

Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dim Cell As Variant
For Each Cell In Range("A1:OL1")
Select Case Cell.Value
Case "Eng1"
Cell.Offset(1, 0).Value = "Engine One"
tons more in the middle here
Case Else
Cell.Offset(1, 0).Value = "N/A"

Rather then returning "N/A", how could I reference the cell above and just copy it instead?

View 9 Replies View Related

Removing Two Letters From A String Of Letters And Numbers

Jul 29, 2014

i have a list of 2000 fields which have the same format IE "AB10014"

I need to remove the "AB" from every field and leave the #.

Besides putting a space and running text to columns I'm not sure how.

View 13 Replies View Related

Macro For Creating Letters To UPPER Letters

Dec 7, 2009

I have words in cell range (i.e. A1:A1000) and I want them to became upper letters.

Excel forum to EXCEL FORUM

View 5 Replies View Related

Remove Letters From A Column Containing Both Numbers And Letters

Jul 24, 2012

I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'

E.g.

frt
34.2
36

p34.5

In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.

View 3 Replies View Related

Lookup Comma Separated Data And Return Comma Separated Answer

Mar 19, 2014

Comma separated data on sheet 2, look up info on sheet 1, return comma separated data on sheet 2.

Sample file attached : Book1.xlsx

View 3 Replies View Related

Case Statment Not Stopping At A Case

Apr 22, 2009

I decided to try to change it into a Case Statement. Here is what I have now. But the problem seems to be this time at this line: When I have "01" in C5 the script just keeps going?

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved