Home Archives for Trim() Function
October 4, 2012 Leave a Comment

How to Correct #N/A Error Messages for Excel’s VLOOKUP Function

Fix the #N/A Error Message

A colleague of mine – who is an experienced Excel user – cannot figure out why he is receiving those ugly #N/A Error Messages when he is using the VLOOKUP Function in Excel.

After a quick look at his workbook, I spotted the problem!

Use the TRIM() Function to Remove Extra Spaces

It turns out, that the data source – housed on the company’s Main Frame Computer – is using “Fixed Width” Fields. So, while only 7 characters are visible, I used the LEN() Function to determine that the field was actually storing 12 characters.

The #N/A Error Message for VLOOKUP()

The #N/A Error Message appears when a Match cannot be found for the Lookup Value in the Left-most Column of the Table_Array when using VLOOKUP(). In this case, my viewer was trying to match a lookup value that contains 7 characters to a value in a field formatted to contain 12 characters. This is what produced the #N/A Error!

Paste Special Values

The final step to correct this problem is to use Copy – Paste Special – Values to replace the Formulas with the values of those formulas.

Essential Excel Skills

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

In this video, I cover three Essential Excel Skills:

  1. Essential Functions – VLOOKUP()
  2. Text Functions – LEN() and TRIM()
  3. Paste Special Options

I cover each of these skills – and more – in greater detail on my new 4-Hour Video Training Resource, “Nine Essential Skills for Excel.”” Click on the links below to learn more about:

Watch Video in High Definition

Follow this link to watch my Excel tutorial in High Definition on my YouTube Channel – DannyRocksExcels

How to Correct #N/A Error Messages for Excel's VLOOKUP Function [ 6:02 ] Play Now | Play in Popup | Download (7514)
Link to this post!
Filed Under: Excel Basics, Excel Functions, Excel Training, Import Data into Excel, Lookup Functions in Excel, Text Functions Tagged: , , , , , , ,
April 6, 2011 2 Comments

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.

How to Use Text Functions in Excel [ 8:28 ] Play Now | Play in Popup | Download (1181)
Link to this post!
Filed Under: Download Files from Video Lessons, Excel Training, Podcast Tips & Timesavers, Text Functions Tagged: , , , , , , , , , , , , , ,
July 23, 2008 Leave a Comment

Use the =TRIM() Function to eliminate unnecessary space inside a cell

Here are the steps to follow in this lesson:

  1. When we wrote the formula =B4&” “&C4&” “&D4)  we found an unnecessary blank space between “First Name” and “Last Name” when the cell reference for “Middle Name” was blank.
  2. Edit that formula by using the =TRIM() Function to remove the extra space in the resulting cell.

Find the video lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Excel Functions, Excel Training, Text Functions Tagged: , ,