Back to Newsletter Archives
Back to The Software Pro Home
============================================================
Smart & Easy Software Tips, Tricks & Techniques
March 2005 - Issue 4, Volume 2
Publisher: Dawn Bjork Buzbee mailto:Dawn@SoftwarePro.com
The Software Pro (tm), Microsoft Certified Office Expert
http://www.SoftwarePro.com
============================================================
IN THIS ISSUE - Microsoft Excel: Working with Text-Part 1
============================================================
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.
1. Formatting Text Cells
2. When Formatting Doesn't Work
3. Combining Two or More Cells
4. Another Use of the Ampersand (&)
5. Wrapping Text
=====================================================
1. Formatting Text Cells
=====================================================
If 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
200, 1000, 33 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 that you can format cells prior to
entering data. This is true for text as well as numbers. To
format entire columns, for instance, for text entry, highlight
the column(s), then choose Format > Cells, choose the Number
tab, and then select the Text format, finish with OK. Apply the
Text format to existing cells as well for better sorting and
filtering results (works best with the latest versions of Excel).
=====================================================
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
=====================================================
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 our example, 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 are 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 that is
used to combine or add multiple entries.
In Excel as well as Microsoft Access, the & is also part of
special codes which means that 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), choose Format > Cells, pick the Alignment Tab,
and check the option for Wrap Text, finish with OK. 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 within the same cell.
=====================================================
Try these tips and tricks to master your text entry in
Microsoft Excel.
------------------------------------------------------------
Please pass this along to others who may appreciate it!
------------------------------------------------------------
For the latest additions to The Software Pro (tm) website:
http://www.SoftwarePro.com/whatsnew.htm
Past newsletter issues are found at:
http://www.SoftwarePro.com/archives.htm
For Website or e-zine questions: mailto:Dawn@SoftwarePro.com
If you are receiving this issue as a forward, and want
your own subscription, visit
http://www.SoftwarePro.com/subscribe.htm
Privacy Policy - We hate receiving spam as much as
you do. Therefore, we never rent, trade, or sell
our email lists to anyone for any reason. You or
someone you know subscribed you to this e-zine list.
We never add names to our list until you voluntarily
subscribe.
(c) 2005 The Software Pro (tm)
All rights reserved.
http://www.SoftwarePro.com
To Subscribe go to:
http://www.SoftwarePro.com/subscribe.htm
============================================================
Geek Speak Becomes People Friendly (tm)
Dawn Bjork Buzbee
The Software Pro (tm)
11675 Bent Oaks Street, Suite 200
Parker, CO 80138-5703 USA
(303) 699-6868, Fax (303) 766-0886
mailto:Dawn@SoftwarePro.com
http://www.SoftwarePro.com