Capital excel что это
Обновлено: 05.07.2024
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.
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
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!
Capitalize Only the First Letter of the First Word Only
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.
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:
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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?
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.
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.
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.
Imagine how this can help us solve our ‘fifa world cup’ problem.
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.
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.
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.
В Excel можно вставлять сведения об акциях и географические данные. Для этого достаточно просто ввести текст в ячейку и преобразовать его в тип данных Stocks (Акции) или Geography (География). Эти два типа данных считаются связанными, так как они связаны с источником данных в Интернете. Такое подключение позволяет получать разнообразные полезные сведения, которые можно использовать и обновлять.
Примечание: Если Excel распознает то, что вы введете, как финансовое или географическое расположение, оно предлагает настроить связанный тип данных ("Акции" или "География").
Сведения о других доступных типах данных можно найти в этой Excel.
Примечание: Типы данных Stocks и Geography доступны толькоMicrosoft 365 или с бесплатной учетной записью Майкрософт. В языковых параметрах Office также должен быть добавлен английский, французский, немецкий, итальянский, испанский или португальский язык редактирования.
Stocks (Акции)
На рисунке выше ячейки с названиями компаний в столбце A содержат тип данных Stocks (Акции). Это известно, потому что у них есть такой значок: . Тип данных Stocks (Акции) подключен к веб-источнику, который содержит дополнительные сведения. Столбцы B и C извлекают эти сведения. В частности, значения цены и изменения цен извлекаются из типа данных Stocks (Акции) в столбце A.
Ищете простой способ получения исторических финансовых данных? Попробуйте функцию STOCKHISTORY.
Geography (География)
Введите текст в ячейки. Если вам нужна информация об акциях, в каждой ячейке введите тикер, название компании или название фонда. Если вам нужны географические данные, в каждой ячейке введите название страны, области, территории или города.
Затем выберем ячейки.
Хотя это необязательно, рекомендуем создать таблицу Excel. Это упростит получение сведений из Интернета. Чтобы создать таблицу, выберите Вставка > Таблица.
Выбирая ячейки, перейдите на вкладку Данные и выберите stocks (Акции) или Geography (География).
Если Excel находит совпадение между текстом в ячейках и веб-источниками, текст преобразуется в тип данных Stocks (Акции) или Geography (География). Вы узнаете, что преобразование было преобразовано, если у них есть значок акций: и значок географии:
Выберите одну или несколько ячеек с типом данных, и появится кнопка данные. Нажмите эту кнопку, а затем щелкните имя поля, чтобы извлечь дополнительные сведения. Например, для акций можно выбрать "Цена", а для географических — "Население".
Примечание: Если вместо вы видите значок, Excel не можете найти соответствие текста данным в веб-источниках. Исправьте орфографические ошибки и нажмите клавишу ВВОД, чтобы повторить попытку. Можно также нажать и появится области выбора. Выполните поиск по ключевым словам, выделите нужные данные, а затем нажмите кнопку Выбрать.
Если вы хотите получить актуальные данные для типов данных, щелкните правой кнопкой мыши ячейку со связанным типом данных и выберите Тип данных > Обновить. При этом будет обновлена выделенная ячейка и все остальные, в которых используется этот же тип данных.
Дополнительные действия
Связанные типы данных подключаются к источнику данных в сети. После преобразования текста в связанный тип данных в книге создается подключение к внешним данным. Если данные изменяются в Интернете, можно получить обновленные данные, обновив их в Excel. Чтобы обновить данные, щелкните правой кнопкой мыши ячейку со связанным типом данных и выберите тип данных >обновить. При этом будет обновлена выделенная ячейка и все остальные, в которых используется этот же тип данных.
Если вы хотите обновить все связанные типы данных и все подключения к данным, которые могут быть в книге (включая запросы, другие подключения к данным и стебли), выберите данные> Обновить все или нажмите CTRL+ALT+F5.
После преобразования текста в тип данных Stocks или Geography в ячейке появится значок. Щелкните его, чтобы просмотреть карточку. На карточке выводится список полей и соответствующих значений. У некоторых данных может быть несколько пар полей и их значений.
Например, на этом рисунке показана карточка для записи France (Франция). Одним из полей, доступных для нее, является Capital (Столица). Его значение — Paris (Париж). Еще одно поле — Leader(s) (Руководители), значениями которого являются имена руководителей.
Если вы хотите увидеть другие пары полей и значений, прокрутите вниз страницу внутри карточки.
Если вы хотите узнать, откуда приходят поля и значения, обратите внимание на заметку Powered by в нижней части карточки.
Читайте также: