by Dawn Bjork, MCT, MOSM | The Software Pro
In addition to numbers and formulas, key data in Microsoft Excel also includes text entries that sometimes needs editing or changes to fit the needs of the Excel project. In this article, learn six ways to stop retyping and save time by using Excel text functions to manage text in your worksheets.
1. Thinking “Outside of the Cell”
Too often, I see Excel users who are retyping or trying to manipulate data within the same cell in order to get the correct format and display. To paraphrase an overused phrase, when you want to transform text in Excel, you will often need to think “outside of the cell,” that is, add extra working columns to your worksheet to store the formulas to give you the results you need. If you already have some form of the data, don’t retype. Instead, automatically create what you need with some of the following text functions. And, once you get the results, you can take it a step further and use the Paste Values option to copy and paste the displayed results into the same cell or a different column.
2. Changing Text Case
What if you have text in cell A1 (or all of column A) that has a mismatch of upper- and lower-case characters such as chris Edwards and you want all entries to be consistent?
In cell B1, type:
=PROPER(A1)to change the data to initial caps: Chris Edwards
=UPPER(A1)to change to all upper case: CHRIS EDWARDS
=LOWER(A1)to change to all lower case: chris edwards
3. Removing Extra Spaces
When working with data originating from other sources, it is common for the data to have leading or trailing spaces which will often affect the success of searches and sorting. To display a text entry without any extra spaces at the beginning or end, use the TRIM function as in =TRIM(A1). TRIM strips extra spaces from text, leaving only single spaces between words and no space characters at the start or end of the text. You can also nest other functions inside of a TRIM function such as: =TRIM(PROPER(A1)).
4. Extracting Only What You Want
Another text entry problem is when you only want to work with a portion of a data entry. What do you do when you to sort or search on characters at the beginning, middle, or end of a text entry?
Let’s look at a simple example on how to extract a portion of the text entry of an accounting code, such as MA8-23456-T445,in cell A1:
=LEFT(A1,3)for the result “MA8” of the first 3 characters from the left
=RIGHT(A1,4)for the result “T445” of the last 4 characters from the right
=MID(A1,5,5)to extract the result “23456” from the middle starting at character position 5 for 5 characters
5. Count the Characters in a Cell
To count the length or number of characters in the cell, use the LEN function. For example, to determine the length of an entry in cell A1, type: =LEN(A1)
6. Pulling Text Apart by Bringing Functions Together
Sometimes Excel data includes names, addresses or other entries you might want to split up for easier sorting and searching. To split up a text entry such as “Chris Edwards” in cell A1, use a combination of functions including FIND which is used to locate specific characters such as a space or comma:
=LEFT(A1,FIND(” “,A1)-1)the result is “Chris”-all of the characters to the left of the space
=RIGHT(A1,LEN(A1)-FIND(” “,A1))the result is “Edwards”-all of the characters after the space
Try these additional tips and tricks to save time and to avoid retyping text in Microsoft Excel. Find more Excel shortcuts, tips, and tricks at TheSoftwarePro.com/Excel.
IAAP works with the best trainers in the industry to ensure you have relevant, engaging, practical content at your fingertips. This blog is written by a speaker with a program in the IAAP Approved Programs database. Search by name or keyword to find their contact information and book them for your Branch or Region event.
If you’d like to attend one of these events, you can find one in your area here.
Dawn Bjork is The Software Pro®, a Microsoft Certified Trainer (MCT), and is a certified Microsoft Office Specialist (MOSM) Master and Microsoft Office expert. Dawn is passionate about sharing smart and easy ways to increase your software productivity through her work as a technology speaker, software trainer, computer consultant, and author of 9 books. Discover more software tips, techniques, and timesavers at TheSoftwarePro.com. Dawn also shares oodles of daily tips on Twitterand Facebook.
IAAP Edge 