Capital excel что это
Apart from using Excel with numeric data, a lot of people also use it with text data. It could as simple as keeping a record of names to something more complex.
When working with text data, a common task is to make the data consistent by capitalizing the first letter in each cell (or to capitalize the first letter of each word in all the cells)
In this tutorial, I will show you a couple of methods to capitalize the first letter in Excel cells.
So let’s get started!
This Tutorial Covers:
Capitalize First Letter Using Formula
There can be two scenarios where you want to capitalize:
- The first letter of each word
- Only the first letter of the first word
Capitalize the First Letter of Each Word
This one is fairly easy to do – as Excel has a dedicated function for this.
The PROPER function, whose purpose of existence is to capitalize the first letter of each word.
Suppose you have a dataset as shown below and you want to quickly convert the first letter of each word into upper case.
Below is the formula you can use:
This would capitalize the first letter of each word in the referenced cell.
Pretty straight forward!
Once you have the desired result, you can copy the cells that have the formula and paste it as values so it’s no longer linked to each other.
Capitalize Only the First Letter of the First Word Only
This one is a little more tricky than the previous one – as there is no inbuilt formula in Excel to capitalize only the first letter of the first word.
However, you can still do this (easily) with a combination of formulas.
Again, there could be two scenarios where you want to do this:
- Capitalize the First Letter of the First Word and leave everything as is
- Capitalize the First Letter of the First Word and change the rest to lower case (as there may be some upper case letter already)
The formulas used for each of these cases would be different.
Let’s see how to do this!
Capitalize the First Letter of the First Word and Leave Everything As Is
Suppose you have the below dataset and you only want to capitalize the first letter (and leave the rest as is).
Below is the formula that will do this:
The above formula uses the LEFT function to extract the first character from the string in the cell. It then uses the UPPER function to change the case of the first letter to upper. It then concatenates the rest of the string (which is extracted using the RIGHT function).
So. if there are words that already have capitalized alphabets already, these would not be changed. Only the first letter would be capitalized.
Capitalize the First Letter of the First Word and Change the Rest to Lower Case
Another scenario could be where you want to change the case of only the first letter of the first word and keep everything in lower case. This could be when you text that you want to convert to sentence case.
In this scenario, you may get some cells where the remaining text is not in the lower case already, so you will have to force the text to be converted to lower case, and then use a formula to capitalize the first letter.
Suppose you have the dataset below:
Below is the formula that will capitalize the first letter of the first word and change the rest to lower case:
Let me explain how this formula works:
- LOWER(A2) – This converts the entire text into lower case
- UPPER(LEFT(A2,1) – This converts the first letter of the text string in the cell into the upper case.
- REPLACE function is used to only replace the first character with the upper case version of it.
One of the benefits of using a formula is that it keeps the resulting data dynamic. For example, if you have the formula in place and you make any changes in the data in column A (the original text data), the resulting data would automatically update. In case you don’t want the original data and only want to keep the final result, make sure to convert the formula to values
Capitalize First Letter Using VBA
While using formulas is a quick way to manipulate text data, it does involve a few extra steps of getting the result in an additional column and then copying and pasting it as values.
If you often need to use change the data as shown in one of the examples above, you can also consider using a VBA code. With a VBA macro code, you just have to set it once and then you can add it to the Quick Access Toolbar.
This way, the next time you need to capitalize the first letter, all you need to do is select the dataset and click the macro button in the QAT.
You can even create an add-in and use the VBA code in all your workbooks (and can even share these with your colleagues).
Now let me give you the VBA codes.
Below code will capitalize the first letter of the first word and leave everything as-is:
And below is the code that will capitalize the first letter of the text and make everything else in lower case:
You need to place this VBA code in a regular module in the VB Editor
These are some methods you can use to capitalize the first letter in Excel cells. Based on the scenario, you can choose the formula method or the VBA method.
В отличие от Word, в Excel нет кнопки смены регистра. Для перевода текста в нижний регистр – например, чтобы вместо «СЕРГЕЙ ИВАНОВ» или «Сергей Иванов» стало «сергей иванов» – необходимо воспользоваться функцией «СТРОЧН» . Преимущество использования функции заключается в том, что вы можете изменить регистр всего столбца текста одновременно. В примере ниже показано, каким образом это сделать.
Вставьте новый столбец возле столбца, содержащего текст, который необходимо преобразовать.Предположим, что новый столбец – это столбец B, а первоначальный столбец – это столбец A, и что ячейка A1 содержит заголовок столбца.
В ячейке B2 введите =LOWER(A2) и нажмите клавишу «ВВОД». Текст в ячейке B2 должен стать строчным.
Заполните этой формулой столбец B.
Теперь выберите преобразованные значения в столбце B, скопируйте их ивставьте как значенияповерх значений в столбце A.
Удалите столбец B, поскольку больше он вам не понадобится.
Верхний регистр
В отличие от Word, в Excel нет кнопки смены регистра. Для перевода текста в верхний регистр – например, чтобы вместо «сергей иванов» или «Сергей Иванов» стало «СЕРГЕЙ ИВАНОВ» – необходимо воспользоваться функцией «ПРОПИСН». Преимущество использования функции заключается в том, что вы можете изменить регистр всего столбца текста одновременно. В примере ниже показано, каким образом это сделать.
Вставьте новый столбец возле столбца, содержащего текст, который необходимо преобразовать.Предположим, что новый столбец – это столбец B, а первоначальный столбец – это столбец A, и что ячейка A1 содержит заголовок столбца.
В ячейке B2 введите =ПРОПИСН(A2) и нажмите клавишу «ВВОД». Текст в ячейке B2 должен стать прописным.
Заполните этой формулой столбец B.
Теперь выберите преобразованные значения в столбце B, скопируйте их ивставьте как значенияповерх значений в столбце A.
Удалите столбец B, поскольку больше он вам не понадобится.
Каждое слово с заглавной буквы
В отличие от Word, в Excel нет кнопки смены регистра. Для преобразования текста таким образом, чтобы все слова в тексте были с заглавной буквы – например, чтобы вместо «Сергей ИВАНОВ» или «СЕРГЕЙ ИВАНОВ» стало «Сергей Иванов» – необходимо воспользоваться функцией «ПРОПНАЧ» Преимущество использования функции заключается в том, что вы можете изменить регистр всего столбца текста одновременно. В примере ниже показано, каким образом это сделать.
Вставьте новый столбец возле столбца, содержащего текст, который необходимо преобразовать.Предположим, что новый столбец – это столбец B, а первоначальный столбец – это столбец A, и что ячейка A1 содержит заголовок столбца.
В ячейке B2 введите =ПРОПНАЧ(A2) и нажмите клавишу «ВВОД». Текст в ячейке B2 должен изменить регистр.
Заполните этой формулой столбец B.
Теперь выберите преобразованные значения в столбце B, скопируйте их ивставьте как значенияповерх значений в столбце A.
How can we change the case of the first letter from lower to upper in Excel cells? Do we have to type each character manually into every cell? Not anymore! Today I will share three methods of capitalizing first letters in your table.
I believe when it comes to text in Excel, one of the most commonly required tasks is to capitalize first letters in the cells. Whenever you have lists of names, products, tasks, or anything else, you will for sure have some of them (if not all) written only in small or capital letters.
In one of our previous articles we discussed how PROPER function can save the day. But since it capitalizes every word in a cell and lowers other letters, sometimes it can't be a cure-all.
Let's see what other options we have on the example of a shortlist of villains I like the most.
Capitalize first letter using formulas
Excel has lots of useful functions suitable for capitalizing first letter in the cells. However, you can't have both, your data and formula that references it, in one cell. Thus, you need to create a helper column somewhere in your worksheet in order to place the formulas there. When it's done, and calculations are made, you'll be able to replace formulas with their values. Shall we begin?
First letter Capital, lower the rest
To make only the very first letter capital in Excel cell and lower the rest at the same time, start with inserting an additional column for the results. In my example it is column B. Right-click the column name (B) and choose Insert from the context menu. The column is inserted between A and C columns, and you can change its header name if there is one:
Tip. It is most likely that the rest of the rows will be populated with the adjusted formula automatically. Otherwise, you can quickly copy the formula down the column by drag-n-dropping or double-clicking that small square in the lower-right corner of the cell with a formula.
Let me explain what the formula above means:
- UPPER(LEFT(C2,1)) converts the first letter of C2 cell to capital.
- REPLACE function is used to make sure that the whole text is returned with one specified letter changed - the first one in our case.
- Adding LOWER(C2) as the first argument of the REPLACE function allows us to lower all other letters:
Thus, you get properly looking cells written as sentences.
First letter Capital, disregard the rest
In order to capitalize the first letter of the cell and leave the other characters as they are, we will use the same formula as above with a little modification.
But first, again, make sure to create another column to use the formula. Then, enter the following into B2:
See, we deleted that "LOWER" part from the beginning of the formula. This little change won't lower all the letters in a cell but will still capitalize the first one:
Capitalize first letter using Text Toolkit: Change Case
If you decide that you need a quicker and faster way of making first letters in Excel cells capital, you will choose wisely!
Our Change Case from Text Toolkit will look over those pretty little letters of yours. It is available in the collection of 60+ toos for Excel - Ultimate Suite:
-
and install Ultimate Suite collection to your PC.
- Run Excel and click the Change Case tool icon in the Text group under the Ablebits Data tab:
The add-in pane will appear on the left side of your Excel window. - Manually select the range of cells where you want to change case, B2:B10 in our case.
Tip. You can select the range before running the tool. It will show the selected range in the corresponding field automatically.
Note. If you want to save a copy of your data just in case, tick off the Back up the worksheet option before making any changes.
Note. When every word in a cell (except the first one) starts with a capital letter, the add-in will not only capitalize the first character, but also lower the rest.
As you can see, capitalizing letters in Excel is not rocket science. Now you can do it in a couple of mouse-clicks and enjoy the results. Feel free to leave comments and ask questions down below :)
In this article I'd like to tell you about different ways to change Excel uppercase to lowercase or proper case. You'll learn how to perform these tasks with the help of Excel lower/upper functions, VBA macros, Microsoft Word, and an easy-to-use add-in by Ablebits.
The problem is that Excel doesn't have a special option for changing text case in worksheets. I don't know why Microsoft provided Word with such a powerful feature and didn't add it to Excel. It would really make spreadsheets tasks easier for many users. But you shouldn't rush into retyping all text data in your table. Fortunately, there are some good tricks to convert the text values in cells to uppercase, proper or lowercase. Let me share them with you.
Excel functions for changing text case
Microsoft Excel has three special functions that you can use to change the case of text. They are UPPER, LOWER and PROPER. The upper() function allows you to convert all lowercase letters in a text string to uppercase. The lower() function helps to exclude capital letters from text. The proper() function makes the first letter of each word capitalized and leaves the other letters lowercase (Proper Case).
All three of these options work on the same principle, so I'll show you how to use one of them. Let's take the Excel uppercase function as an example.
Enter an Excel formula
- Insert a new (helper) column next to the one that contains the text you want to convert.
Your formula should look like this =UPPER(C3) , where C3 is the cell in the original column that has the text for conversion.
Copy a formula down a column
Now you need to copy the formula to other cells in the helper column.
- Select the cell that includes the formula.
- Move your mouse cursor to the small square (fill handle) in the lower-right corner of the selected cell until you see a small cross.
Note: If you need to fill the new column down to the end of the table, you can skip steps 5-7 and just double-click on the fill handle.
Remove a helper column
So you have two columns with the same text data, but in different case. I suppose you'd like to leave only the correct one. Let's copy the values from the helper column and then get rid of it.
Here you are!
This theory might look very complicated to you. Take it easy and try to go through all these steps yourself. You'll see that changing case with the use of Excel functions is not difficult at all.
Use Microsoft Word to change case in Excel
If you don't want to mess with formulas in Excel, you can use a special command for changing text case in Word. Feel free to discover how this method works.
- Select the range where you want to change case in Excel.
- Press Ctrl + C or right-click on the selection and choose the Copy option from the context menu.
- Open a new Word document.
- Press Ctrl + V or right-click on the blank page and select the Paste option from the context menu
Note: You can also select your text and press Shift + F3 until the style you want is applied. Using the keyboard shortcut you can choose only upper, lower or sentence case.
Now you have your table with the text case converted in Word. Just copy and paste it back to Excel.
Converting text case with a VBA macro
You can also use a VBA macro for changing case in Excel. Don't worry if your knowledge of VBA leaves much to be desired. A while ago I didn't know much about it as well, but now I can share three simple macros that make Excel convert text to uppercase, proper or lowercase.
I won't labor the point and tell you how to insert and run VBA code in Excel because it was well described in one of our previous blog posts. I just want to show the macros that you can copy and paste into the code Module.
If you want to convert text to uppercase, you can use the following Excel VBA macro:
To apply Excel lowercase to your data, insert the code shown below into the Module window.
Pick the following macro if you want to convert your text values to proper / title case.
Quickly change case with the Cell Cleaner add-in
Looking at the three methods described above you might still think that there is no easy way to change case in Excel. Let's see what the Cell Cleaner add-in can do to solve the problem. Probably, you'll change your mind afterwards and this method will work best for you.
-
and install it on your computer.
With Cell Cleaner for Excel the changing case routine seems to be much easier, doesn't it?
Besides changing text case Cell Cleaner can help you to convert numbers in the text format to the number format, delete unwanted characters and excess spaces in your Excel table. Download the free 30-day trial version and check out how useful the add-in can be for you.
Video: how to change case in Excel
I hope now that you know nice tricks for changing case in Excel this task will never be a problem. Excel functions, Microsoft Word, VBA macros or Ablebits add-in are always there for you. You have a little left to do - just choose the tool that will work best for you.
There are times when letter case in Excel needs to be changed or corrected.
Well, I’m here to share the good news: You don’t have to do it manually at all! Excel happens to offer a few functions that allow us to address letter case in a much more effective manner.
Kasper Langmann, Co-founder of Spreadsheeto
There are two functions that are most notable: ‘UPPER’ and ‘LOWER’. They do exactly what their names imply.
There is also a function that capitalizes the first letter of proper names and places. It is called, ‘PROPER’.
All three have their uses and make life a lot more convenient for us Excel users.
There might be some (rare) cases, where these functions aren’t going to provide a solution. Don’t worry, I’ll show you exactly what to do in those cases.
Kasper Langmann, Co-founder of Spreadsheeto
For now, let’s set the foundation by learning how to use the ‘UPPER’, ‘LOWER’, and ‘PROPER’ functions on their own.
*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.
Table of Content
Get your FREE exercise file
Before you start:
Throughout this guide, you need a data set to practice.
I’ve included one for you (for free).
Download it right below!
Download the FREE Exercise File
Capitalize the first letter using
the function ‘PROPER’
The ‘PROPER’ function works the same way its cousins ‘LOWER’ and ‘UPPER’ do.
The difference is, that it only capitalizes the first letter of each substring of text. This could be a single word. It could also be multiple words such as first and last names, cities and states, abbreviations, suffixes, and honorifics/titles.
Kasper Langmann, Co-founder of Spreadsheeto
The syntax for all three of the functions that change case is the same.
It’s simply the function and the text (or cell reference) within parenthesis.
Here’s the syntax for ’PROPER’:
Now let’s look at some examples of text strings and the results of applying the ‘PROPER’ function to them.
Notice that the function takes string elements whether they are capitalized or not. Then it converts the entire string to proper form. This is quite straightforward.
But there are a couple of issues to be aware of when using the ‘PROPER’ function…
The main issue is that it does not know the difference between an actual word and an abbreviation – like an acronym for instance. For example, if we apply the ‘PROPER’ function to something like ‘FIFA’, it will return ‘Fifa’. This is not the desired outcome and should be kept in mind.
Kasper Langmann, Co-founder of Spreadsheeto
Another example of this would be using the suffix ‘md’ for a medical doctor. Again, ‘PROPER’ will see this more as a word and will return ‘Md’.
If you want both letters capitalized, place a period after each letter in the abbreviation.
Or apply the ‘UPPER’ function to just that part of the original string.
Capitalizing all letters using
the function UPPER
Now that we know how to capitalize the first letter of a text string, let’s find out how to make all caps.
This requires the ‘UPPER’ function and as we already noted, its syntax is much like that of ‘PROPER’.
Consider a case in where we have a worksheet that contains names of customers. This gets exported out of a database in all lowercase form. No problem, we just bring the ‘UPPER’ function into the game.
Kasper Langmann, Co-founder of Spreadsheeto
Apply ‘UPPER’ to our cell references containing the customer names to make all caps.
Changing uppercase to lowercase
using the function ‘LOWER’
If you have a list that comes as all caps, you can use the ‘LOWER’ function to convert to all lower case.
Like this:
These are three of the simplest functions to use in Excel and they sure come in handy when the need presents itself.
But what about something a bit less straightforward?
Kasper Langmann, Co-founder of Spreadsheeto
Using case functions together
for customization
It’s one thing to learn the basic functions and tools that Excel offers.
But it’s far more fun and fulfilling to piece these together to create our own custom solutions.
That’s exactly what we are going look at now…
Think back to what you learned about abbreviations. That’s right, they tend to create some issues! The three functions you’ve learned about, can’t solve these issues on their own.
Kasper Langmann, Co-founder of Spreadsheeto
Let’s say we want to fix the string ‘fifa world cup’ to read ‘FIFA World Cup’.
This requires us to split the string into separate substrings, namely ‘fifa’, and ‘world cup’.
Here we could use the ‘UPPER’ and ‘PROPER’ functions, respectively.
We could split our original string manually and apply the function we need on each substring.
But then we would need to reunite the results of each separate function to get our final result.
For the sake of demonstration, let’s look at how combining these functions with a couple of others can achieve the same results.
Kasper Langmann, Co-founder of Spreadsheeto
Quick intro to
the functions ‘ LEFT ’ and ‘ RIGHT ’
For anyone who is not familiar with these two functions, they are very simple in concept and easy to pick up.
Pro-tip: I explain LEFT and RIGHT (+other text functions) in much more in-depth right here
They do exactly what they sound like they do:
They return a substring of characters from the left or right side of our original text string.
Both require one argument: ‘text’.
This is the text or cell reference to apply the function to.
There is also an optional argument: ‘num_chars’. This is the number of characters from the left or right that we want the function to return. (If we omit this argument, the functions return the first character from the left or right.)
The formal syntax for each is as follows:
Look at the following simple examples to see how these functions work.
Kasper Langmann, Co-founder of Spreadsheeto
Imagine how this can help us solve our ‘fifa world cup’ problem.
First, we should be aware of something…
… Since the first four letters of our string need to be all capital letters, we want to isolate this part of the original string.
Then we’re going to apply the ‘UPPER’ function to it.
We can isolate those four letters by using ‘LEFT’.
‘=LEFT(“fifa world cup”, 4)’
This gets us the substring result of ‘fifa’.
Since we know this we can enclose the above formula within the ‘UPPER’ function to get ‘FIFA’:
‘=UPPER(LEFT(“fifa world cup”, 4))’
Be mindful of your parentheses when nesting functions like this. It is very easy to overlook a closing parenthesis and this will result in an error. Let’s see how all this looks in Excel.
Kasper Langmann, Co-founder of Spreadsheeto
In the following figure, we have used the cell reference ‘B2’ in the ‘UPPER’ function (cell ‘D2’).
This is the same as ‘=LEFT(A2,4)’ since that is the formula in cell ‘B2’ resulting in ‘fifa’.
The next thing we need to do is extract the remaining substring after ‘fifa’ and apply the ‘PROPER’ function to it. We need to know how many characters from the right we want.
Kasper Langmann, Co-founder of Spreadsheeto
Since the substring ‘world cup’ is 9 characters in length (including the space), our formula will be:
‘=RIGHT(“fifa world cup”, 9)’
In much the same way, the ‘LEFT’ function parsed ‘fifa’ for us, our ‘RIGHT’ function parses out ‘world cup’.
Then like we did with ‘fifa’ we need to enclose our ‘RIGHT’ formula within the ‘PROPER’ function.
Putting it all together requires a bit more finesse.
Join our two substrings by using the ‘&’ and an extra space between them.
I know this is starting to get a bit advanced.
My point is to introduce you to the further possibilities. It takes some practice.
But, these advanced techniques are worth learning to get the most out of your letter case functions.
Not all challenges will be as simple as using ‘PROPER’, ‘UPPER’, and ‘LOWER’ on their own. By seeing what is possible, you can leverage these functions as tools for solving bigger challenges.
Kasper Langmann, Co-founder of Spreadsheeto
Читайте также: