Back to Newsletter Archives
Back to The Software Pro Home

============================================================
Smart & Easy Software Tips, Tricks & Techniques
August 2005 - Issue 11, 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 2
============================================================
In the March 2005 issue on Excel, we looked at a variety of tips for formatting text in Excel. Read these tips and tricks at:
http://www.softwarepro.com/archives/march2005-4.htm

Now, learn more about how you can save time by using formulas instead of retyping to work with text in Microsoft Excel.

1. Thinking "Outside of the Cell"
2. Changing Text Case
3. Removing Extra Spaces
4. Extracting Only What You Want
5. Count the Characters in a Cell
6. Pulling Text Apart By Bringing Functions Together

=====================================================
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, you will want to add extra working columns to store the formulas that will 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 formulas.

=====================================================
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 that originated from other sources, it is common that the data may have leading or trailing spaces that will 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))

=====================================================
How much more productive can you be with Microsoft Excel?

Bring customized Microsoft Excel programs and coaching into your organization. Master linked worksheets, managing data lists, automate key tasks with macros, learn great tips, tricks, and shortcuts...create your own training or personal coaching delivered by The Software Pro...add valuable education to your next conference or meeting...maximize your use of Microsoft Excel.

Contact us to let us know how we can help you:
http://www.softwarepro.com/contact.htm

=====================================================
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 that 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.

------------------------------------------------------------
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 sp^m as much as
you do. Therefore, we never rent, trade, or sell
our e-mail 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 or e-mail
any message to mailto:join-softwarepro@mh.databack.com

============================================================
Geek Speak Becomes People Friendly (tm)

Dawn Bjork Buzbee
The Software Pro (tm)
Post Office Box 5319
Englewood, CO 80155-5319 USA
(303) 699-6868, Fax (303) 766-0886
mailto:Dawn@SoftwarePro.com
http://www.SoftwarePro.com

To remove your address from this distribution:
http://mh.databack.com/c.php?L=softwarepro&E=#e-mail#