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: Working with Text  

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: 5 Tips to Work With Text

Important data in Microsoft Excel is not just numbers and formulas but also text. Whether the text is a column heading or a critical data element, Excel includes many ways to improve the layout, appearance, and value of text. Find out how to format text cells, combine data from multiple cells, and other text formatting tricks.

1. Formatting Text Cells

Excel text tips, shortcuts, working with text in Excel, Excel 2010, Excel 2007If you are having problems sorting or filtering data lists, one of the reasons may be that some data in a column is formatted as text and some as numbers. In an ascending sort order, numbers and punctuation will sort before text. For instance, entries of 22, 1111, 333 would sort as 22, 333, 1111 if formatted as a number, and 1111, 22, 333 if formatted as text (left-to-right). You will get inconsistent results when a combination of formats exists. The Text format is also needed for data that includes a fixed number of places or leading zeros such as 0001234.

One plus in Excel is you can format cells prior to entering data. This is true for text as well as numbers. To format entire columns for text entry, highlight the column(s), then right-click on the column and choose Format Cells. Next, pick the Number tab from the dialog box, and then select the Text format, finish with OK. In Excel 2010 and Excel 2007, the Text alignment choice is also found on the Home tab (Alignment group) of the Ribbon. Apply the Text format to existing cells as well for better sorting and filtering results.

2. When Formatting Doesn't Work

To change a single entry to a text format, type an apostrophe (') in front of the data.

3. Combining Two or More Cells (Sounds Like Cat)

Existing data may not be formatted exactly the way you need. For example, if employee names are in 2 columns, Last Name and First Name, but you want to display a full name for reporting as in Last Name, First Name (Smith, Joe) there are several ways to do this. In the following examples, Column B is Last Name and Column C is First Name. To create the Full Name in Column D, the formula would be similar to (either example works):

=CONCATENATE(B2,", ",C2) <----- CONCATENATE is a function that "combines"; additional text like the comma is included in quotations.

=B2 & ", " & C2 <----- the & also combines the values in cells B2 and C2.

4. Another Use of the Ampersand (&) in Excel and Access

In the above example, the ampersand (&) is a character used to combine or add multiple entries. In Excel as well as Microsoft Access, the & is also a special code which means a single ampersand won't work in most text entries (the result is _ instead). To use an ampersand in a header, footer, or label in Excel or Access, type 2 ampersands, such as TIPS && TRICKS.

5. Wrapping Text

For multiple line entries of labels, comments, notes, or descriptions in Microsoft Excel, select the cells (or an entire row or column), right-click on the selection and choose Format Cells, pick the Alignment Tab, and check the option for Wrap Text, finish with OK. In Excel 2010 and Excel 2007, Wrap Text is also found on the Home tab of the Ribbon. The column width will determine how many lines are needed to display the text. Choose this over multiple rows for lengthy text entries for greater readability and for any lists of data. Once text is formatted to enable wrapping, you can further control the layout by pressing [Alt] + [Enter] to force a line break or new line within the same cell.

Try these tips and tricks to master your text entry in Microsoft Excel.

>>Microsoft Excel: 6 More 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