Archives for April 2011

How to Use Text Functions in Excel

Text Functions in Excel

Text Functions

In my experience, I find that many of my clients do not realize that Excel has many powerful – and easy to use – Text Functions.

Even more amazing, I observe clients spending hundreds of hours and thousands of dollars “re-keying” or “manually editing” text entries that have been downloaded from a Main Frame Computer. If you – or your colleagues – have been doing this, you will want to watch this Excel Video Tutorial.

Categories of Text Functions in Excel

In this Excel video lesson, I demonstrate how to use three categories of Text Functions:

  1. Transform Text – Use the UPPER, lower and Proper functions to change the “Case” of a text string.
  2. Join Text – Use either the CONCATENATE() Function or the & (Ampersand) Operator to join two or more text strings into a single text string.
  3. Clean-up Text – Use the LEN(), TRIM() and CLEAN() Text Functions to “trim the fat” from text strings – especially those that you download from a Main Frame Computer.

Once again, in my experience, I have too many clients contacting me in a panic because they get a REF# error when using Text Functions. I demonstrate how to recover from a “seeming disaster” and also how to use COPY – PASTE SPECIAL – VALUES to transform Formulas into Constant Values.

I welcome your feedback. Please send me an email: danny@thecompanyrocks.com – to let me know if this tip helped you to save time and money. I also welcome your suggestions for future Excel Video Lessons to create and post here, on my website.

Watch This Excel Video Tutorial in High Definition Mode

Follow this link to view this Excel Video Lesson in High Defintion Mode on my YouTube Channel – DannyRocksExcels

Download the Excel Workbook

Follow this link to download the Excel Workbook I use in this lesson.

Learn How to Master Excel in Minutes – Not Months!

If you like this Excel Video Tutorial, I invite you to learn more about the resources that I offer to help you to improve your Excel skill set.

Learn How Excel Calculates Formulas

Order of Calculations in Excel

Excel Calculation Order

Have you ever written a Formula in Excel only to receive a result that was different from the one you expected? Most Excel users have shared that experience from time to time! Excel is smart and fast. However, it can not read your mind.

In this lesson, I show you how Excel evaluates and calculates a formula. Once you understand “Excel’s perspective” of the formula, you will know how to correctly write the formula so that Excel will produce the result that you intended. In other words, to see the formula from “your perspective!

Order of Precedence

Excel performs calculations in formulas moving from left to right in this order:

  1. Performs Multiplication (*) and Division (/)
  2. Goes back and performs Addition (+) and Subtraction (-)

This is the “key” to consistently getting Excel to produce the results that you intended. No, you can’t get Excel to make you an “instant millionaire” or do anything illegal. However, understanding how to write formulas correctly – so that you control the order of calculation in Excel – is the “secret sauce!”

Control the Order of Calculation in Excel This formula: =5+15*2 results in 35. If you were expecting the result to be 40, then write the formula as (5+15)*2. In other words, take the “result” of 5+15 or 20 and multiply this by 2 to give me 40.By using parentheses (5+15) you take control over the order of precedence that Excel uses. Help Excel to see the formula from your perspective!

Free Chart of Excel Formula Operators

I have published  a chart – “Using Operators in Excel Formulas” – To get your free copy as a PDF, click on the link below:

CR – Using Operators in Excel Formulas

Explore My Free Excel Video Lessons

Follow this link to my Index of Free Excel Video Tutorials

Watch This Video Tutorial In High Definition

This link will take you to the DannyRocksExcels video on YouTube

How to Search For and Use Text Strings in Excel

A viewer asked for my help in creating a report that will group his customers’ email addresses by their “domain name.” For example, he wants to be group all customers with gmail addresses . Likewise, his  customers’ yahoo mail, aol.com accounts, etc.

Pivot Table for Email Domains

Pivot Table Email Domains

I immediately decided to use two powerful Excel tools to produce this report:

  1. Text-to-Columns to create two columns (name and domain) from one text value (the email address).
  2. Pivot Table Report to group and present customer emails by domain name.

Both tools work great and I produced the report with only a few mouse clicks!

The response that I got from my viewer was, “That’s great Danny. But …”

It turns out that the viewer wanted to have the Domain Names as the Field Headers and to show each customer’s email address as a row going down vertically in the corresponding field. To give my viewer the report that he wanted, I used these tools and techniques:

  • I returned to the Pivot Table to copy the unique list of domain names.
  • I used Paste Special – Transpose to convert the vertical list of domain names into a horizontal row of Field Headers.
  • I began to build my “Mega-formula” by taking “baby steps” – i.e. I created one formula and then “nested” it inside additional formulas and functions.
  • For the first “baby step” formula I used the SEARCH() Function.
  • Next, I “nested” this formula as the “logical test” inside an IF() Function.
  • In order to “hide” all of the formula error signs, I nested both of these functions inside the NEW IFERROR() Function.
  • IFERROR() was introduced in Excel 2007 and I now consider it to be “indispensable!” This function, alone, makes upgrading to either Excel 2007 or Excel 2010 a “nobrainer” decision!

I enjoy answering questions from my viewers. Many of these questions can be answered from one of the lessons on my DVD-ROM, “The 50 Best Tips for Excel 2007.” For the rest, I try to create a video lesson that demonstrates how to get the answers that you – my viewers and customers – are looking for.

Keep your questions and suggestions coming! I enjoy receiving feedback from you!

I invite you to subscribe to my RSS Feed on Feedburner to be automatically notified whenever I publish a new Excel Video Tutorial.

Watch this Excel Video in High Definition on YouTube

Here is the link to watch this video on my YouTube Channel – DannyRocksExcels