Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
When you import data into a spreadsheet, You may come across mixed-letter cases, or words with all lowercase or uppercase letters. We’ll demonstrate how to make your Excel text consistent if you want to preserve the consistency of your data.
Three functions are available: the Flash Fill function, an Office add-in and a method to cut and paste between Microsoft Excel or Word. You can choose the method that is most convenient or best for your needs.
To Change a Case, Use this Formula
Excel formulas can be used, regardless of whether you are already using them These are three useful functions For changing the case of text. A simple formula allows you to adjust text by using the UPPER, LESSER and PROPER functions.
You will need to apply the Excel functions to other cells than the ones containing data. For example, in the new column to your right.
The UPPER Function
The UPPER function is used to convert your text to use all uppercase letters. You can use the syntax UPPER (text), where you can either enter your actual text or the cell reference.
We’ll use this formula to change the text, which is a combination of upper- and lowercase letters, into all caps:
=UPPER(“joe smith”)
It is important to note that the text of the argument should be enclosed in quotation marks. You can also use any letter case.
Another example: We’ll make cell A2 all-uppercase with the following formula:
=UPPER(A2)
Function for the LOWER
LOWER, on the other hand of the UPPER function, is what you want. It allows you to modify all lowercase text. It is called LOWER(text), where you can again enter the cell reference or just the text.
We will use this formula to change A3 text to lowercase letters:
=LOWER(A3)
Be sure to put the text inside quotation marks if you want it to be used as an argument. This will change the text from lowercase to uppercase by changing it in quotations:
=LOWER(“john jones”)
THE PROPER Function
The PROPER function allows you to capitalize the first letter in each word. This is often known as title case. This function is very useful Names of the first and last names, Headlines, titles and/or descriptions. The syntax is the same as PROPER (text), but it has some modifications.
We’ll be using the below formula to make the title case text by inserting it into quotes:
=PROPER(“jerry white”)
This formula will be used to modify cell A4’s text:
=PROPER(A4)
The Formula can be copied
Copy and paste the formula into multiple columns if you don’t have enough cells.
Click on the cell that contains the formula. Drag the plus sign (fill handle) down into the cells below.
The formula will adjust to correctly reference the cells and then fill out the result.
Note: Update the references automatically.
Use Flash Fill to Take Advantage
The Excel’s Flash Fill function You can also use Flash Fill to modify the font case. Flash Fill allows you to create an entry that is exactly the same as your text, and fill it with Flash Fill.
This is where you will find a collection of names using mixed-letter cases. Then, enter your text in the cells to the right. Press the button Enter or Return.
You can then either choose to use keyboard shortcut Ctrl + E Or, you can choose the Flash Fill In the Data Tools Section of the website, click the button Data tab.
As if by magic, the cells that are next to the other entries will fill up to show the same as the original.
Alternativ, you may also select Flash Fill Although it appears that the button accepts suggestions officially, it isn’t necessary.
Take a look at an add-in
Perhaps you don’t like formulas or prefer a tool to modify the text right there in place. One of Excel’s addins is available for you to try. This is the Swap Case Add-in.
- Use the Get Add-ins The button is located on the Insert click on tab to open Office Add-ins Stor.
- Enter “Case” and “Swap Case”, then click “Search”. You can choose Add All the best! Continue To install the add-in.
- Go back to the worksheet, and start picking Swap Case The following are the Home tab.
- If you do not see the item, please open the My Add-ins Drop-down menu on the Insert Tab, Select See All, Add.
- The add-in sidebar will open on the right. Select the cells that you wish to modify and choose either the lowercase or uppercase button.
As you change the case of the selected cells, the text within them will be changed.
This add-in has the advantage of allowing you to change the text inside the original cells. However, there is no option for a true case.
Microsoft Word – Copy and Paste
This option is available to you if you are a frequent user of Microsoft Word and Excel. Word allows you to easily change the text case. You can also edit it in Word and then import it into Excel.
- You can copy cells in Excel that have the text you need to edit. You can accomplish this by using Ctrl + C, Copy The button is located on the Home Tab, or by right-clicking and choosing Copy.
- Open a Copy and Paste Word documents Data. It is possible to do so using Ctrl + V, Paste The button is located on the Home Tab, or by right-clicking and choosing Paste.
- Once your data is displayed, click on the text to open it and then use the Change Case Click the button to access the drop-down menu Home Tab to select the case that you prefe.
- The next step is to select the Word data and then copy it with one of the Copy actions.
- You can return to Excel, paste your data and use one of the paste options above.
You will then be able to see the edited text in whatever case you choose.
This method is more labor intensive than the others, but it can still be useful if you are comfortable with it and doesn’t require an additional column.
Excel lets you change the text case one at a time. You have two options. These are quicker, simpler, and more efficient. Which option will you choose to use?
You can find more instructions at How to Excel duplicates can be removed.
