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: , , , , , , , , , , , , ,