how to make upper case after mark "." (dot), see this below :
sample data
after formula
Tabrani,Ny.yusmaniar
Tabrani,Ny.Yusmaniar
Badu Zai,Tn.surjaah R & Darussamin
Badu Zai,Tn.Surjaah R & Darussamin
Zainal Arifin,Ny.elvyati
Zainal Arifin,Ny.Elvyati
etc..
it's possible with excel code? if could perhaps not macro.
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"
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.
Is there a way to get Excel to automatically change the first letter of single word entry to lowercase in each cell of the first row of my worksheet leaving the case the same for all other letters?
View 5 Replies View RelatedHow do you find if the last character in a string is a quotation mark?
View 9 Replies View RelatedIn sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
i have a string in a cell, that would say "Your results are -10% for the week". is there any way to change the font to red, for the number, including the minus sign preceeding it, and the percent sign following it. all are in one column, about a thousand rows. the rest can stay black.
View 5 Replies View RelatedI am looking for a while for a macro or a function, which should change needed letters in string into needed ones.
Example:
A1 cell contains words "John Johnson Martinson". I want to switch "n" letter into my specified, lets say "m" and the result should be "Johm Johmson Martimsom".
I would like to get it within range of columnA. Is it possible to get?
Is there a way to get Excel to automatically change the first letter into an uppercase when we start a new sentence just like MS Word ?
View 9 Replies View RelatedI have about 5000 rows with mixed upper & lower case words. I am trying to get only values if all words are upper-case. I have office 2013.
View 12 Replies View RelatedI have created a data entry spreadsheet for staff.
I need to format all data entry field to uppercase.
Is there a way to format some particular colums so that when i enter my data into the cells i do not have to keep changing the caps lock to uppercase or pressing shift key which slow down my typing as i am not very fast typer.
View 4 Replies View RelatedMy question is about determining whether or not a particular letter is uppercase.
I've written code that reads from a text file one character at a time. And I know that the following determines if each character is equal to the letter "a".
Is there a way to find and pull out uppercase letter in a cell..
Example Below!
Sheet7
AB18Micheal Johnson JonesM. J. J.19United States of AmericaU. S. A.20Laugh Out LoudL. O. L.21North Dakota N. D.
Excel tables to the web >> Excel Jeanie HTML 4
I'm Using Windows Xp Home Edition With Sp2 With Excel 2003!
how to tackle this in excel vba using a macro. I want to force the first letter of each cell in Column A to uppercase until it reaches a blank
View 2 Replies View Relatedi have a worksheet called 'week 1' and want to write vba code so that a new sheet called 'week 2' can be created. However i want to write code so that it doesn't matter what sheet i am on, a new sheet would be created with the name week and the next number up. ie. the last worksheet is 'week 7' then the code should be able to create a new worksheet called 'week 8' ect. I currently have this code but it keeps returning an error message. anyone know how i could do this???
Sub nSheet()
Dim nm, i As Long
nm = Worksheets(Worksheets.Count).name
i = 1
Sheets.Add
Do While WorksheetExists(Left(nm, Len(nm) - 1) & i)
i = i + 1
Loop
ActiveSheet.name = Left(nm, Len(nm) - 1) & i
End Sub
How to change a "ddd' formatted cell and change it to a text string (even if you have to use another set of cells)?
I have 3 columns of cells A,B,C.
Column A has dates in it.
Column B has the "ddd" format of the A cells (takes date from A cell and turns it into the day of the week...ex:2/17/2012 into "Fri")
Column C is the one I want to formatt whatever is in the B column into a string, such as "Fri" but with it being text instead of "ddd" formatted.
I am trying to accomplish the following, when i enter a word in column C but in the odd row i want a macro to automatically convert that word to uppercase.
View 2 Replies View RelatedI am looking for a formula that would extract uppercase words from a cell. I have cells containing this kind of sentences:
"FROZEN PRODUCTS;Frozen avocado;FROZEN FISH;SweeT things;"
so I started with this Macro to extract uppercases and punctuation:
Public Function majuscules(zone)
Dim sel As Object
Dim i As Integer
Application.Volatile
For Each sel In zone
[Code] ........
It works pretty well but gives all the uppercases, and what I want is only those of words fully in upper.
Is there a way to tweak this formula so it gives all the uppers that touch another upper.
In the example case, it should return:
"FROZEN PRODUCTS; ;FROZEN FISH; "
The little cherry on the cake would be if it could give me:
"FROZEN PRODUCTS; FROZEN FISH"
I am using the code below to convert all input on the spreadsheet to UPPERCASE. This was working fine until I added a Column that contains dates (Column D is formatted like 03/Mar/2009). So when I type in 03-03 in column D I get: 03/Mar/2009 instead of 03/MAR/2009
Can the code be tweaked to accommodate the dates?
Private Sub Worksheet_Change(ByVal Target As Range)
'Converts ALL input to UPPERCASE
Dim cel As Range
Application.EnableEvents = False
On Error Resume Next
For Each cel In Intersect(Target, Target.SpecialCells(xlCellTypeConstants, xlTextValues))
cel.Value2 = UCase(cel.Value2)
Next cel
Application.EnableEvents = True
End Sub
i have a form that i have 2 fields that need to be capatalized at all times - B6 and G6 I have used this formula to get B6 to work but for the life of me i cant get BOTH to work and the same time.
here is the formula
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("B6") 'change to your range
If Not IsEmpty(Target) Then
Target = UCase(Target.Text)
End If
End Sub
This is wrekcing my head as I'm sure it can be done with an Array formula and I cant seem to get it sorted -
Say I have 5 Rows of text starting in A2
"A quick word"
"A quick WORD"
"A"
"word"
"WORD"
I need an excel formula to say "False", "False", "False", "False", "TRUE".
All conditions to be met for TRUE are - Word must in uppercase, Uppercase Word must be longer than 1 character, Sentence (i.e. cell) can not contain a space. This is the path I've gone down, but almost certainly incorrect -
=IF( FIND(CHAR(64+COLUMN($A$1:$Z1)),$A$2,1),TRUE,FALSE)
[CTRL+SHIFT+ENTER]
I am trying to make a column of names appear in upper case (the first letter). I know about Upper(). I cant put that formula in this column because the names are typed there, (it would erase formula). How can I do this as well as have them alphabetically ordered?
View 2 Replies View RelatedI import a CSV file into Excel where the column title row has column titles that are just one long text string, without any spacing between the words. For example:
CompanySiteDescription
CompanySiteExternalSystemID
IssueNumber
I would like a method (formula or macro) that would add a space-character before each uppercase letter (that's not the first letter in the string or an uppercase letter that directly follows another upper case letter). Thus:
CompanySiteDescription becomes Company Site Description
CompanySiteExternalSystemID becomes Company Site External System ID
IssueNumber becomes Issue Number
I need to change the colour of a cell using VBA based on the input of a certain word. I have used teh below code but it doesn't pick up different variations of the word
Dim cell As Range
For Each cell In Range("E2:E500")
If cell.Value = "Check" Then[code]....
This code works fine (i have put it into worksheet change) when I use "Check and Mark and Chase" but some users are bound not to use capitalised first letters and on these occasions the cell colours are not changing.
How to add something to thie code above to make it work for any variation of capitalisation?
I have a button (shape) whose text value is linked to cell A1. (In other words, if text in cell A1 changes, the text on the button changes). A concatenation formula exists in cell A1 to "join" together a combination of text and cell values. The formula in A1 looks something like this:
="You have "&B10&" records that contain errors."
I'd like to set the font for the "&B10" portion to be a different color than the rest of the text string so that it stands out from the rest of the text. Obviously this can be done when the text is manually entered into a text box, but when using a formula to populate the text in the text box, it's all or nothing. I'm hoping to find a way to embed a separate font color within the formula string in A1 for just the portion I want to change? For example, all text would be regular/black, but the numeric value generated from cell B10 would be yellow.
Formula that will convert uppercase words longer than three letters, into lowercase. Leaving the rest of the string unchanged?
For example,
Mary and John at IBM thought this was a GREAT OPPORTUNITY. Becomes:
Mary and John at IBM thought this was a great opportunity.
I have also tried using more func's REGEX.SUBSTITUTE but I couldn't get that to work either!
To ensure correct data entry, I need to ensure that the filled cells in a column are always in Uppercase for example, irrespective of whether they are typed in upper or lower case.
I have found the following code, which seems to work quite well, even though I have the impression that it slowed my workbook down slightly.
I was using this code in a sheet that contained combo boxes and it worked great. When I redone the sheet a used data validation instead of CB it wold error out.
Code:
Sub savee()
Range("A4:J22").Select
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = UCase(Cell.Value)
End If
Next Cell
Range("h4").Select
ActiveWorkbook.Save
End Sub
Is there Code for forcing all text in a certain range to Uppercase?
Example: A1:E20
if possible I need the code to be just inserted into the sheet code. Maybe were when the user moves to another cell it changes them then.