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
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 .
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 RelatedI 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
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?
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.
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
=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.
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
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"
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.
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
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
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
Is there a way of selecting text and converting it all from lower to upper case?
View 3 Replies View RelatedI 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.
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] .....
I have created a text convertor which will convert any text into upper case or lowercase (not very complex but very handy!) Is there anyway way that when text is in uppercase and I convert it to lower case that it makes every character after a full stop in upper case
Example
DAVID WAS NOT WELL. HE WENT HOME
to
David was not well. He went home
I currently get
david was not well. he went home.
way to split a text line into seperate cells using upper case letter, or any letter for that matter, as delineator. A couple of sample lines would be as follows. It happens to be in upper case, but doesn't need to be, so any alphabetical letter would do, I think.....
View 9 Replies View RelatedIs it possible to make a spreadsheet so that everything that is typed into it is in CAPITALS? I have a need for that for a spreadsheet that I am using at work, but I remembered that in the title box above it only capitlaizes the first letter. I thought if it can do it with the first letter can Excel do it with all of the letters.
View 3 Replies View RelatedA sample macro has EXACTLY the effect that I want, but contains absolute references. I want it to operate on a string selected by the mouse. How can I make that conversion ?
The successful macro will change the case of text I select to lower case.
I have many shortcut words to make full name using Auto Correct option under Proofing
I am using this code to make force range in Uppercase
[Code] ....
But when i put a auto correction value in small case "ip" {like ( IP = IRFAN PAT )}
Then the cell value is going in uppercase "IP" but auto correction is not working
I want ..if i type in range "ip" result shoul be (" IRFAN PAT ") not "IP"
The values in F4:F14 are changed depending on the value in F2. The rows in green have a fixed value. The row in yellow has a lower limit (170)and upper limit (195). I cannot get a formula in F14 to perform this function.
View 3 Replies View RelatedI 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] .....
I am attempting to change a range of cells from lower case text to all upper case text.
View 4 Replies View RelatedI have a range of numbers:
A1=24
A2=17
A3=9
A4=4
Based on this the MEAN=13.5 and STANDARD DEVIATION= 8.81286937760152
I want to create a formula to calculate the UCL and LCL
When I use MINITAB I get UCL=31.23 & LCL=-4.23
I want to make a line chart that plots a variable over time (say, monthly) and that shows upper and lower limits as the 2nd and 3rd plot. My table layout, boiled down to the bare essentials:
Col 1 = Measurement date
Col 2 = Measurement (pressure, for example)
Col 3 = High limit (never varies)
Col 4 = Low limit (never varies)
The chart/graph in this case would show at a glance how water pressure varies over time and when it goes over (under) a limit. The table has about 50 variables.
Im looking to use vba to determin upper and lower quartiles, i know how to do this through excel "QUARTILE(B9:B59990,1)", but was wondering if anyone would of any code to do this in visual basic?
View 2 Replies View RelatedI want to generate random times in excel between upper and lower limits , which i can do , but i also want dates in same cells with out randomization , means dates should act independently and time should randomize
it should be something like that
8/1/2013 05:55 AM
8/2/2013 07:55 AM
8/3/2013 04:44 AM
I have a table that I use to generate about 20 graphs. The lower and upper limits can be manually altered in the table. If the user leave those 2 cells empty, I want the upper and lower limits be determined by default by Excel. So that the limits don't have to be entered for every graph in order for the graphs to be generated without any debugging error.
With ActiveChart.Axes(xlValue)
.MinimumScale = y1Lower
.MaximumScale = y1Upper
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
where
y1Lower = .Cells(i, j + 9)
y1Upper = .Cells(i, j + 10)
