How do I make the first letter of a string uppercase, but not change the case of any of the other letters? How do I make the first letter of a string uppercase, but not change the case of any of the other letters? Because OP didn't asked for code that capitalizes the first letter in the entire string. Enter this formula: =PROPER(A2) into a blank cell next to your data, B2 for instance, see screenshot: 2. And then drag the fill handle down to the cells that you want to apply this formula, and you can see all the first letters of each word within the cell have been capitalized. Free apps for mac. And Remember The Milk has an import tool as well for bringing your data in from Wunderlist. While Microsoft intends to keep Wunderlist going for a while, if you use Wunderlist you have the task of moving to another to-do app someday. FOLLOW for all the latest app news. Microsoft offers an importer tool to transfer Wunderlist data to To-Do. Hello I'm struggling on this, I'm afraid I'm a complete novice. (Despite spending ages researching I'm still not confident I even know how to insert into VBA a selection of a range of columns properly!). What I'm trying to do is capitalise just the first letter of cells in my columns F - P. I don't want to change the other words (whether they be capitalised or not). I also don't want to affect the whole sheet, but just this range. (There are also random blank cells in each column and the final non-blank row numbers of each varies and the columns will be growing. The current final value for one of the columns is on row 515 and it is unlikely it will grow to more than, say, 3000). I tried adapting this from post 2 here: and so. Code: Sub CapsFirstLetter() Dim Cell As Variant Range('F:P').Select For Each Cell In Selection Cell.Formula = UCase(Left(Cell.Text, 1)) & LCase(Right(Cell.Text, Len(Cell.Text) - 1)) Next End SubJust so you know why you are getting the error. When Cell is empty, it length is 0 and 0-1 equals -1 which is an invalid number for the second argument to the Right function (you cannot retrieve -1 characters from the text), so it errors out. The solution is to use the Mid function and make use of the fact that VB's Mid function retrieves all the text from the start position specified in the second argument to the end of the text when the third argument is omitted (how I wish Excel's MID function did that) as Mike shows you in Message #2. Different printer types. Also, if you're uncomfortable using VBA for whatever reason, see my formula below. You would have to find an empty spot in your worksheet to put the formula, then drag it across and down to create an array of desired size. Then, simply copy the results and paste values only over the original text. Code: Sub RemoveSpaces2() Dim R As Range Dim WS As Worksheet Set WS = ActiveSheet With Application.EnableEvents = False.Calculation = xlCalculationManual.ScreenUpdating = False End With For Each R In WS.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues) R.Value = Application.WorksheetFunction.Trim(R.Text) Next R With Application.EnableEvents = True.Calculation = xlCalculationAutomatic.ScreenUpdating = True End With End Sub(taken from ) That would be the cherry on the cake! I could probably create a macro to run both but I thinking it would be better/faster to combine the two? Thanks for all your help. Excel often tries to compensate for your shortcomings as a typist. For instance, as you are typing along, if you type a word where the first two letters are uppercase, and the next letter is lowercase, Excel figures you just have slow fingers and didn't release the Shift key in time to make the second letter lowercase. So, it dutifully changes the second letter to lowercase to help you out.
0 Comments
Leave a Reply. |