Blog Archives

Tom’s Tutorials For Excel: Evaluating Numbers as Being Whole or Decimal

Tom’s Tutorials For Excel: Evaluating Numbers as Being Whole or Decimal

There are times when you want to identify a number as being a whole number (such as 47) or a decimalized number (such as 23.5).

The picture shows three ways to apply this idea.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String

Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String

When you are faced with alphanumeric strings, such as those esoteric-looking serial numbers that represent a store’s stock items, here is how you can deal with parsing them based on the position of their first integer.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding the Number Closest to Zero

Tom’s Tutorials For Excel: Finding the Number Closest to Zero

Here are two formulas, one to tell you the number closest to zero in a list, and the other to tell you the address of the cell holding that number. When you know a list does not contain a zero (if it did,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Counting Words in a Sentence or String

Tom’s Tutorials For Excel: Counting Words in a Sentence or String

Here is a formula to count the words in a sentence or string of text.
=IF(LEN(A2)=0,0,LEN(A2)-LEN(SUBSTITUTE(A2, ” “, “”))+1)

The SUBSTITUTE function handles the possibility of the cell containing no text or value.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Extracting Text to Left of the Second Space (or Specified Character) in a String

Tom’s Tutorials For Excel: Extracting Text to Left of the Second Space (or Specified Character) in a String

Previously, I posted this example of extracting text to the left of the first space, or of some specified character.

When you need the first two words in a string,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name

Tom’s Tutorials For Excel: Using a Formula to Get Your Active Worksheet’s Name, and Active Workbook’s Path and Name

Here are two formulas, one to return the active worksheet’s name, and the other to return the active workbook’s full path and name. In each case, please be sure to save the workbook at least once.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Listing Column Letters Across and Down

Tom’s Tutorials For Excel: Listing Column Letters Across and Down

Here are formulas to display the column letter in any individual cell, or to list column letters horizontally across a row, or vertically down a column.

As shown in this first picture, you can display any cell’s column letter with the formula
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),”1″,””)

You can use that same formula,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Extracting the Last Word in a Cell

Tom’s Tutorials For Excel: Extracting the Last Word in a Cell

When you want to extract the last word in a cell, be it a last name or whatever the word or character is that follows the last space in a string, this formula can do that,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Converting Formula References From Absolute to Relative

Tom’s Tutorials For Excel: Converting Formula References From Absolute to Relative

I previously posted this manual example, and this programmed example for converting formula references from relative to absolute. The manual example showed how the row and / or column references can be kept or omitted.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Finding and Replacing Wildcards

Tom’s Tutorials For Excel: Finding and Replacing Wildcards

In this previous Tom’s Tutorial blog post, I showed an example of using wildcards in formulas. Today’s Tutorial shows how to find and replace wildcard characters themselves on your worksheet.

To review, in Excel,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Extracting the First Word or String in a Cell

Tom’s Tutorials For Excel: Extracting the First Word or String in a Cell

Here is an example for returning the first word in a cell, with a formula that can be easily modified to return all contents to the left of the first found specified character in a cell.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Exporting Code to Find and Replace

Tom’s Tutorials For Excel: Exporting Code to Find and Replace

Following up last week’s blog entry about exporting code from a module to a text file, here is how to create a copy of that text file and make changes to the code for Find and Replace.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Exporting VBA Module Code to a Text File

Tom’s Tutorials For Excel: Exporting VBA Module Code to a Text File

Here’s a quick way to export all the VBA code from a workbook module, assuming for example:
• The module of interest is named Module1.
• The text file that will receive this code is named “YourFileName.txt”.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Summing Numbers When Combined With Text

Tom’s Tutorials For Excel: Summing Numbers When Combined With Text

Because you are reading this, chances are you’re the go-to person in your workplace who’s counted on to work miracles in Excel.

In the picture, an ill-advised data entry practice shows a quantity of 3 production items,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: Delete Rows With Blank Cells

Tom’s Tutorials For Excel: Delete Rows With Blank Cells

Sometimes you have a list for which you need to delete rows where a cell is blank in a particular column. If the list is small, you can eyeball each blank cell, select it, and delete the row manually,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,

Tom’s Tutorials For Excel: COUNTIF and SUMIF with wildcards

Tom’s Tutorials For Excel: COUNTIF and SUMIF with wildcards

You can use wildcard characters with functions to return specific information. For example, the wildcard “?” character represents any single character. The wildard “*” character represents any count of characters.

To see wildacrds in action,

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials for Excel: TRIM and CLEAN your data

Tom’s Tutorial For Excel: TRIM and CLEAN your data

Sometimes you will receive output files from data warehouse applications, or you will copy data from a web page onto your Excel worksheet. Many cells can look jumbled with the data they hold, because of extra (and usually invisible) spacebar or ASCII characters that were not meant to be a part of the actual data.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,

Tom’s Tutorials for Excel: How to Create or Replace a Worksheet

Tom’s Tutorials for Excel: How to verify a worksheet exists, create if not.
The following examples show how to determine if a worksheet exists, and then how to create or replace the worksheet

Determining if a Worksheet Exists

This example shows how to determine if a worksheet named “Sheet4” exists by using the Name property of the Worksheet object.

Read more ›

Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,