Physical Address
304 North Cardinal St.
Dorchester Center, MA 02124
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.
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 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)
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 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)
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.
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.
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.
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.
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 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.