Microsoft Office 2010 Expert, Office 2007 Expert, software speaker, software training, consulting, software tips & tricks, Office 2007 trainer, Microsoft Word, Microsoft Excel, Microsoft PowerPoint, Microsoft Access, Microsoft Outlook, tips, Microsoft Office 2007 trainingSoftware Tips & Tricks, Expert Speaker, Microsoft Office 2010 Training, Microsoft Certified Trainer, Microsoft Office Consultant, Books, Videos, Web Training  
 Home Products Programs Meet Dawn Free E-zine Media Room Contact 
Excel Text Tips & Functions  

Technology Trainer
Software & Tech Speaker
Software Consultant
Tips & Tricks
The Pro Shop
Free Software Articles
How-To Lessons
Tips Newsletter
Bonus Resources

 
Dawn Bjork Buzbee
The Software Pro

Microsoft Certified Trainer (MCT)

Certified Microsoft Office 2010 Specialist (MOS 2010) Master Instructor

Certified Microsoft Office 2007 Specialist (MOS 2007) Master Instructor

Microsoft Certified Application Specialist (MCAS) Instructor

Certified Microsoft Office Expert

Certified Women's Business Enterprise (WBE)
WBENC Certified

WOSB (Women-Owned Small Business) Certified

(303) 699-6868
Dawn@TheSoftwarePro.com

>>Back to Articles Home

Microsoft Excel: 6 More Tips to Work With Text

In addition to numbers and formulas, key data in Microsoft Excel also includes text entries. Learn more about how you can stop retyping and save time by using functions to work with text in Excel.

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...just automatically recreate with some of the following text functions.

2. Changing Text Case

excel text functions, excel tips, TRIM function, LEFT function, RIGHT function, PROPER, UPPER, LOWERWhat 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). This function will not remove spaces between words in a text entry. 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 the simple example of an accounting code such as MA8-23456-T445 in cell A1:

To extract a portion of the text entry, in cell B1 type: =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.

>>Microsoft Excel: 5 Tips to Work with Text

Dawn Bjork Buzbee, MCT, The Software Pro

Dawn Bjork Buzbee is The Software Pro and a Microsoft Certified Trainer (MCT) as well as a certified Microsoft Office Specialist (MOS) Master Instructor, certified Microsoft Applications Specialist (MCAS) Instructor, and a certified Microsoft Office expert. Dawn shares smart and easy ways to effectively use software through her work as a software speaker, trainer, consultant, and author of 8 books.

This article and more can be reprinted at no charge in your publications and website with copyright and attribution.
>>Learn more about how easy it is to share these valuable tips, tricks, and techniques.

More Microsoft Excel Tips, Tricks & Handouts Too!

More Smart & Easy Software Tips, Tricks & Techniques for You

<<Back to Articles
 


Be an Excel Expert!
How effectively are you using Excel to create, format, and analyze your important data? Become an Excel expert with the
Excel with Excel Learning Series. Master functions, filtering, linking, PivotTables, charts and graphics, macros, and more!
Click Here to Choose Your Excel Training

Go To Top of Page
 



Home for Dawn Bjork Buzbee, The Software Pro
Contact Dawn Bjork Buzbee, The Software Pro
Hot Excel Tip
To change a single entry to a text format, type an apostrophe (') in front of the data.
 
Did You Know?
Insert the TRIM() function around formulas to remove extra spaces that might surround a text entry

 
www.TheSoftwarePro.com
HomeContact Us | Training | Speaking | Consulting | Services | Tips & Tricks | Products | How-To Lessons | Free Articles | Newsletter | Resources | Site Map

Copyright 2015 The Software Pro & Dawn Bjork Buzbee - All Rights Reserved
(303) 699-6868
Dawn@TheSoftwarePro.com www.TheSoftwarePro.com
Geek Speak Becomes People Friendly

Microsoft Certified Trainer (MCT)
Certified Microsoft Office 2013 Specialist (MOS 2013) Master
Certified Microsoft Office 2010 Specialist (MOS 2010) Master Instructor
Microsoft Certified Application Specialist (MCAS) Instructor
Certified Microsoft Office Specialist (MOS) Master Instructor
Certified Microsoft Office Expert
Certified Women's Business Enterprise (WBE) WBENC Certified
WOSB (Women-Owned Small Business) Certified
The Software Pro
is a registered trademark
Terms & Conditions       Privacy Policy