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


ADVERTISEMENT

Change All Text From Lower To Upper Case

Jul 11, 2012

I have a workbook on which I want to change all the text from Lower to Upper case.

How can I do this without retyping .

View 4 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

Change Case In Upper And Lower

Sep 6, 2005

=upper("venkat") returns VENKAT
=upper(a1) returns the text in A1 in upper case

similary lower
proper will turn the first letter into uppercase

is this what you want;.

Terry <terrybetts11138@hotmail.com> wrote in message
news:dbl0dk$poa$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Win XP Pro
> Office 2003
>
> Using "Excel" and wish to select any text in a worksheet to ALTER the case
> to either upper or lower.
> When using "Word" it is easy via the menu.
> Is there a menu driven option in Excel
of using a function for this purpose.

View 14 Replies View Related

Change Case From Lower To Upper

Sep 7, 2005

find the "add in" that converts the case text
from lower to upper in excel. i've already downloaded this before but can't
remember where from and it is much easier than creating a formula or a macro

View 9 Replies View Related

Upper/Lower Case In Text

Oct 14, 2009

I have data in cell A1 as below:
36, TREE ROAD, 5TH FLOOR

I want it in proper format in cell A2 as below:
36, Tree Road, 5th Floor

I used formula =Proper(A1), it gave results as:
36, Tree Road, 5Th Floor

The only problem I face is after any number, the next character should in lowercase, i.e. 5th

View 9 Replies View Related

Values In Cells That Contain Text In Both Lower And Upper Case

Sep 10, 2008

I am using the Find fuction to look for values in cells that contain text in both lower and upper case. I am comparing the values that I have stored in a lookup table. I am running into trouble when the lookup table doesn't match the text cells....

View 9 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

Only Allow Upper/Lower Case Not All Upper Or All Lower

Oct 8, 2008

Is there a data validation that will not allow all upper case or all lower case characters?

For examples:

Allow:
John Doe
101A N Woodbine Street

Disallow:
JOHN DOE
101A N WOODBINE STREET

Disallow:
john doe
101a n woodbine street

Or perhaps formatting that would convert the words to upper/lower case?

Or, I would be interested in VBA if it is the only way.

View 9 Replies View Related

Upper And Lower Case In VBA

Sep 22, 2008

I have this code (this is just a snippet)

For Each cel In Range("E6:AI15").Cells
If IsError(cel.Value) Then
Else
Select Case UCase(cel.Value)
Case "H"
cel.Font.ColorIndex = 0
cel.Interior.ColorIndex = 3
Case "S"
cel.Font.ColorIndex = 0
cel.Interior.ColorIndex = 10

but I want the cells to accept both upper and lower case values and format the cells accordingly ie. H or h, S or s

View 9 Replies View Related

Upper Case To Lower Case And Lower Case To Upper Case?

Nov 17, 2009

if there's any way for vba to detect if each individual character in a string is in caps, and if so, convert to lower case, and if it's in lower case, convert to capitalized

The text will vary in length and content... so he wants to see how we can change:
"This Is Strange" to "tHIS iS sTRANGE"
"THIS IS STRANGE" to "this is strange"
"this is strange" to "THIS IS STRANGE"

View 2 Replies View Related

Cell Based On Lower Upper Case

Oct 20, 2009

I've got a column of data that I need to split into further columns, the only way of identifying the split from the data is if a lower case character appears next to an upper case character.

For example, a cell in column A could look like;

Computer ProductsDrivesDVDExternal
And I'd need to split that out to cover columns A B and C with

Computer Products / Drives / DVDExternal
I'd need to do that for all the cells in column A.

View 9 Replies View Related

Lower Case To Upper Case

Jun 20, 2008

When I use a simple formula such as:

=upper(a1)

that will obviously change whatever is in a1 to Upper Case - but it will put it in the cell that holds the formula.

What I want to know is:
Is there any way I can format the cell to run the formula when the information has been pasted into the spreadsheet

View 9 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 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

Excel 2003 :: VBA Left To Work With Upper Or Lower Case?

Apr 8, 2013

I have the macro below, how do I make it NOT be case sensitive, I want it to work for test. Test, TEST

Excel 2003

Code:
Sub test()
If Left(ActiveCell, 4) = ("test") Then
MsgBox "Activecell starts with test"
End If
End Sub

View 3 Replies View Related

Delete Rows Where Columns Have Specific Lower/Upper Case Characters

May 20, 2008

I am trying to detete rows that have several specific values, upper and lower case, (A-H) in any of several columns (B through H). I can delete upper and lower case "D"s in column B, but I'm having difficulty stringing together several variations and getting at the "D"s that are midstream (such as: ADeC), I tried ("*D*") to no avail.

With . Cells(Lrow, "B")
If Not IsError(.Value) Then
'****
If LCase(.Value) = LCase("D") Then .EntireRow.Delete

View 5 Replies View Related

Change Character UPPER TO LOWER

Jul 21, 2014

I need to change the all Upper Case character to Lower case character for all cells. See below my code, i am getting error message, Tye Mismatch. "cell.Value = LCase(cell.Value)"

[Code] .....

View 3 Replies View Related

Bulk UPPER Or LOWER Change

Apr 13, 2009

I am attempting to change a range of cells from lower case text to all upper case text.

View 4 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

How To Change Lower Case To Uppercase

Apr 28, 2009

How to change the lowercase letters to uppercase letters?

While using the userform, for example "name" is entered in the form.

My requirement is the values should reflect as uppercase letters like "NAME"

View 2 Replies View Related

Change Value From Uppercase To Lower Case

Jan 22, 2010

I am trying to change string values of a name from uppercase to lowercase. The data is layed out as lastname then first name. As an example, I have the following values in column C:

ANDERSON MICHAEL
SMITH SUSAN P
JOHNSON PATRICK
JORDAN MICHAEL R
WOODS TIGER
CLINTON WILLIAM J

I am trying to convert to following (with "," between lastname & firstname):

Anderson, Michael
Smith, Susan P
Johnson, Patrick
Jordan, Michael R
Woods, Tiger
Clinton, William J

I have a start of how I think the code should flow but can't figure out how to do the conversion.

View 4 Replies View Related

How To Change And Entire Sheet To Upper Case

Jan 28, 2007

Is it possible to change and entire sheet to Upper case using the 'Upper' function?

We are need to change all the text to caps but can only seem to apply the function to one cell at a time and then copy it down for the column. This is going to be a fairly labour intensive process as we have many columns.

View 12 Replies View Related

Change Cells In Column To Upper Case

Oct 28, 2009

How can I make this code fire for column 2 AND column 3?

View 2 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

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

Excel 2010 :: Change To Lower Case And Manually Align To Left

Sep 4, 2013

I'm using Excel 2010. When I type 'true' or 'false' in any cell, Excel automatically changes to CAPITAL and Align Centre. I have to use the function 'lower' to change to lower case and manually to align to the left.

How to change the default setting.

View 1 Replies View Related

Convert Text To Upper Case

Jul 18, 2014

Is there a way of selecting text and converting it all from lower to upper case?

View 3 Replies View Related

Automatically Capitalize Text Upper Case?

Dec 3, 2004

I would like a user to be able to enter text into a cell in any case and have it be displayed in all capital letters (or as a "Proper" capitalization). Seems strange that >FORMAT doesn't seem to have a "case" selection.

Past threads show VB code to do this, but I don't want the user to have to click a button to change case. Could the VB code run automatically after the user hits >enter, or tab, or an ->? Is there a way to >FORMAT a cell to do this change? I know the "Upper" & "Proper" functions but it would be very clumsy to have the user enter data in cell A1 just to be displayed in capitals in cell A2.

Example: When user types "lax" in cell A1, I would like "LAX" to be displayed in cell A1.

View 8 Replies View Related

IF Text In A Cell Is UPPER Case Or Mixed

Jan 28, 2014

I'm trying to formulate:

If it's mixed case, copy it. If it's all lower case or ALL CAPS, then make it PROPER.

i.e., if 'bananas' is in A1 and B2 says =PROPER(A2), then B2 says Bananas, because PROPER capitalises the first letter of each word.

I would like to do

[Code] .....

View 5 Replies View Related







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