Worksheet Microsoft Excel
Wednesday, May 25th, 2011Worksheet Microsoft Excel
Microsoft Excel: 6 Tips to Work With Text
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
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). 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 tips and tricks to save time and to avoid retyping text in Microsoft Excel.
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 6 books. Discover more software tips, tricks, tactics, and techniques at http://www.SoftwarePro.com.
About the Author
How can I export data from a table from yahoo groups into a microsoft excel worksheet?
1. Save the webpage as a file on the desktop.
2. Open the file in Excel
3. The table will show up in cells, and you can clean up the formatting in Excel.
Worksheet Microsoft Excel
Microsoft Excel Worksheet Protection
Microsoft Excel's Disaster Discovery
I still remember, the first lesson in my computer class, trainer started with the following advice: "Always save your data early and often. Sometimes things fall apart quickly before you even have a chance to react". It is always a good habit to always save your document as soon as you create it. However, you will never know when your computer or software is going to behave abnormal, crash or shutdown.
As a Microsoft Excel user, you are lucky enough as it comes with an invaluable safety feature called AutoRecover. The AutoRecover saves backup copies of your Excel worksheet periodically in the background while you are working with it. If for any reason, Excel crash or the system hang, you can retrieve the final backup of AutoRecover even if you never managed to manually save the file.
Of course, you cannot expect AutoRecover to save up to the last second of the information you entered to the spreadsheet you were working on. However, if the if AutoRecover backup in every 10 minutes, which is the default setting, in the worst case scenario, you will lose 10 minutes of work. Anybody who experienced of losing the entire document without saving the file they were working on, surely will appreciate this handy feature provided by Microsoft Excel.
If your does undergo a system or operating system crash, after you reboot the computer, you can quickly recover your previous spreadsheet file that the AutoRecover last backup. What happen was every time when a user launches Microsoft Excel application, the software will automatically checks the backup folder. If there is any backup found in the folder, Excel opens a Document Recovery panel, which will be located on the left of the Excel window.
If the Excel or computer crashes while you are editing the backup file, the next time you open Microsoft Excel, you will notice that there are the same file listed twice in the Document Recovery window. The difference between these two files is the status. The status with [Original] indicates it is the last version of the file that you saved. This is the original version which is safely stored on the hard drive. While the other version which shows [AutoSaved], indicates the most recent backup created by Excel.
Click on the backup file listed in the Document Recovery window to open it. Note that there is a drop-down menu on the right of each file listed. You can click the drop-down menu for additional options.
Again, it is a good practice to save the backup file that you just opened before you start to work on it, because it is just a temporary backup. If the file that you are trying to open is already corrupted, then Excel will automatically attempts to repair it. You can also choose Show Repairs to look at a list of changes Excel had to perform to recover the file.
For more information about Excel visits:
1. Excel-Application.com
2. Excel Learns.com
About the Author
Alfanso is a software programmers who works as a freelance web developer. He likes to explorer different software with nice features
|
|
Introduction to Microsoft Office Excel 2007 - Part 2 $12.99 ... |
|
|
Microsoft Excel 2010 $120.99 Microsoft Excel 2010 065-06962 Business Management Software... |
|
|
Microsoft® Excel® 2007 Quick Reference Guide - Excel 304: Creating Worksheet Macros & VBA Basics $7.95 This is an 8-page bi-fold quick reference guide. This quick reference guide will help you create, edit and troubleshoot macros using the macro recording feature as well as using basic VBA functionality. Recording your steps is straightforward and can save you time and effort. Once you record your steps, you can run the macro, assign it to an icon on the Quick Access Toolbar, or use a keyboard sho... |
|
|
Mr. Excel - Excel Trace Precedents & Dependents $99.00 "Excel Trace Precedents and Dependents is an invaluable utility for following and auditing large Excel workbooks. Choose a formula and quickly navigate to each cell named in the formula. Use this utility to error-check large formulas in Excel. Works with internal and external references precedents and dependents in all open workbooks. Requirements: Excel 2002 or Newer"... |
|
|
USB Stress Button - Perfect Stress Relief at Work $15.00 USB Stress Button - Perfect Stress Relief at Work General Features: USB interface Three (3) selectable stress reliefs Atomic bomb explosion with countdown Fake worksheet Boss beating 4-foot USB cable Sound mute option... |
|
|
Excel 2010 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) $25.00 All the methods and tools you need to successfully program with ExcelJohn Walkenbach's name is synonymous with excellence in computer books that decipher complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows you how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf.Featuring a complete introducti... |
|
|
Excel 2007 For Dummies $12.22 One look at Excel 2007, with its new Office Button, Quick Access toolbar, and Ribbon, and you realize youâre not in Kansas anymore. Well, have no fearâ Excel 2007 for Dummies is here!If youâve never worked with a computer spreadsheet, or if youâve had some experience with earlier versions of Excel but need help transitioning, here youâll find everything you need to create,... |
|
|
Excel 2010 Formulas (Mr. Spreadsheet's Bookshelf) $28.36 Take your Excel formulas to the next level with this updated referenceJohn Walkenbach's name is synonymous with excellence in computer books that decipher complex technical topics. Known as "Mr. Spreadsheet," Walkenbach provides you with clear explanations on all the methods you can use to maximize the power of Excel with formulas within the frameworks of all the new features of Excel 2010.You'll... |
Worksheet Microsoft Excel
