Microsoft Excel 2019 is the new bet that Microsoft has developed for the management of large amounts of data in a centralized way where administration options abound, we know that Microsoft Excel is full of functions and formulas that will make each data worked a unique experience of management..
Although Microsoft Excel is one of the most complete applications at the level of tools and functions , it lacks some basic ones such as converting text to uppercase or lowercase. This is useful for editing titles, legends or others in the active sheet, unlike Microsoft Word, in Excel we do not have a Change case button to carry out this task, but since Excel is an app based on multiple functions, We can use the functions SHIFT, MINUSC or NOMPROPIO to automatically convert existing text from lowercase to uppercase, from uppercase to lowercase, or to the format of proper name. These functions are integrated formulas that have been designed to carry out specific tasks within the Excel sheet.
We will learn to convert text to uppercase or lowercase through these formulas in Excel 2019 but the same process applies to Excel 2016..
1. How to change upper and lower case in Excel 2019
Step 1
For this case it will be necessary to insert a column next to the column that contains the text we want to convert, for this we right click on the column and select "Insert"
Step 2
For this first example we will capitalize all the data in column A, then, we will use the NOMPROPIO function which allows you to convert the names in capital letters to proper names, so that only the first letter of each name will be capitalized.
In cell B2 we enter the following formula:
= NOMPROPIO (A2)
Step 3
This formula can be dragged to the other lower cells and so it will be copied, as a result we see that the uppercase values ​​become proper names:
Step 4
Now, if we want to convert all the text to lowercase, we must use the MINUSC function, this is responsible for converting the selection to lowercase, in cell B2 we will enter the following:
= MINUSC (A2)
Step 5
We drag the entire formula back to the other cells so that it is copied and this will be the result:
Step 6
On the contrary, if we want all the text to be converted to uppercase, we must use the SHIFT function like this:
= SHIFT (A2)
As a result we will obtain the following:
2. How to use Microsoft Word to convert text in Excel 2019 or Excel 2016
Step 1
Microsoft Word integrates a function to convert text in a much simpler way and we can use it to convert our Excel 2019 text, for this we select the range to convert in Excel and proceed to copy it using the Ctrl + C keys or right click / Copy:
Step 2
Now we open a new Word document and there we use the Ctrl + V or Edit / Paste keys to insert the range of Excel 2019 that we have copied:
Step 3
Now, we select the text to convert, we go to the Start menu and in the Source group we click on the "Change uppercase and lowercase" icon and a series of options will be displayed:
Step 4
In this case we select the option "Capitalize Every Word" and the result will be as follows:
Step 5
Again we can copy this entire range and paste it into Excel 2019 with the new changes made:
3. How to convert text in Excel 2019 using VBA macros
We may use a VBA macro to automate the task of converting our text from uppercase to lowercase or vice versa.
Step 1
To insert a VBA macro in our Excel 20q9 sheet, we will press the Alt + F11 keys to open the macro editor and there we will right click on our current sheet and select the Insert / Module option:
Step 2
In the new window we can enter any of the following macros:
To convert text to uppercase
Sub Uppercase () For Each Cell In Selection If Not Cell.HasFormula Then Cell.Value = UCase (Cell.Value) End if Next cell End Sub
To convert text to lowercase
Sub Lowercase ()
For Each Cell In Selection If Not Cell.HasFormula Then Cell.Value = LCase (Cell.Value) End if Next cell End Sub
To convert the text to your own name
Sub Propercase () For Each Cell In Selection If Not Cell.HasFormula Then Cell.Value = _ Application _ .WorksheetFunction _ .Proper (Cell.Value) End if Next cell End Sub
Step 3
In this case we will enter the macro to convert the text to uppercase:
Step 4
We must save the macro and validate that in the Type field this activates the Excel workbook option enabled for macros:
Step 5
Once the macro is saved, we use the Alt + F8 keys to access the macro menu and there run our macro, we can see that the text is converted to uppercase as defined:
4. How to change text to uppercase or lowercase using add-ins in Excel 2019 or Excel 2016
Another of the useful options available for working with text in Excel 2019 is using add-ons which are extras that increase Excel functionality. In this case we will download and install an add-on called Cell Cleaner which we can download from the following link:
Cell cleaner
Once installed, we must restart Microsoft Excel and we will see a new menu with this add-on. There we must select the text to convert and in the complement menu select the Change Case option where the following options will be displayed:
UPPERCASE
Convert text to uppercase
Lower case
Convert text to lowercase
Proper case
Convert the text to its own name (first letter of each word in uppercase)
Sentence Case
Convert the text to a sentence type (the first letter of the paragraph in uppercase)
In this case we have converted the text to uppercase and we will obtain a result of how many cells have been affected:
We can see that, although Excel 2019 does not integrate the option to modify the default text in its menu, we have multiple alternatives to perform this task in a complete and functional way and thus manage the text in the spreadsheets.