Blog Archives

Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates

Tom’s Tutorials For Excel: Doing Date Math on Text (non real) Dates

In yesterday’s tutorial, I showed how to place a date and time on separate lines in the same cell.

That example involved a formula with the TEXT function,

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: Showing Text Only by Hiding Numbers

Tom’s Tutorials For Excel: Showing Text Only by Hiding Numbers

You can custom format a cell to show or hide only positive numbers, negative numbers, zeros, or text. Here, a range of cells is formatted to show only text with the custom format ;; (that is,

Read more ›

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

Tom’s Tutorials For Excel: Entering Fractions In Your Text

Tom’s Tutorials For Excel: Entering Fractions In Your Text

While it’s true that you can insert fraction symbols from the Symbol dialog box into your cell’s text, it’s faster and easier to produce those symbols by using the keyboard as you are typing your text.

Here are three examples that show how you can use the keyboard to enter the common fractions for one-fourth,

Read more ›

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

Tom’s Tutorials For Excel: Returning text with your formula’s numeric results

Tom’s Tutorials For Excel: Returning text with your formula’s numeric results

I previously posted this example with text, and this example with dates, to show how a custom format can display text in cells without affecting the actual underlying values of those cells.

Read more ›

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

Tom’s Tutorials For Excel: Selecting Only Constants or Formulas

Tom’s Tutorials For Excel: Selecting Only Constants or Formulas

Sometimes you want to do something — such as format, delete or edit — all cells containing constants or formulas at the same time. Here’s how to select all such cells so you can thereafter do to them what you want.

Read more ›

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

Tom’s Tutorials For Excel: Adding Text With a Custom Format

Tom’s Tutorials For Excel: Adding Text With a Custom Format

There are times when you want to append text to a cell’s existing text, without using a helper column with a concatenation formula, and without any programming methods.

As you know, formatting a cell only changes the visual appearance of the cell;

Read more ›

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

Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing

Tom’s Tutorials For Excel: Formatting Cells Containing Formulas, Constants, or Nothing.

With Conditional Formatting, you can color-code cells in real time that contain formulas, or constants, or nothing at all.

Step 1
From your keyboard press Ctrl+F3 to add a new name to your workbook.

Read more ›

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

Tom’s Tutorials For Excel: Summing The First Nth Numbers in a Mixed List

Tom’s Tutorials For Excel: Summing The First Nth Numbers in a Mixed List

Crazy requests can arise when dealing with crazy data. Here you need to sum the first three numbers that appear in a list that contains numbers, text, and empty cells.

The array formula in pink-colored cell E2 is
=SUM(SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{1,2,3})-1,)))

Recall,

Read more ›

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

Tom’s Tutorials For Excel: Couple of Concatenation Conundrums

Tom’s Tutorials For Excel: Couple of Concatenation Conundrums

Here are two examples of concatenating cells for whacky but not unheard-of situations.

The first example is alternating cells in column being concatenated in successive cells.
The formula in cell B4 and copied down to cell B21 is:
=OFFSET(A1,ROW()-1,)&IF(ISBLANK(OFFSET(A1,ROW()-1,)),””,” “)
&OFFSET(A2,ROW()-1,)
Note that I broke the single-line formula into two lines here,

Read more ›

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

Tom’s Tutorials For Excel: Conditional SUM of Alphanumeric Data

Tom’s Tutorials For Excel: Conditional SUM of Alphanumeric Data

Here’s another crazy example — but it happens — when you are faced with making mathematical sense of numbers within alphanumeric data.

This example shows how to sum numbers that follow a certain set of characters,

Read more ›

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

Tom’s Tutorials For Excel: Formatting Numbers and Text With Custom Colors

Tom’s Tutorials For Excel: Formatting Numbers and Text With Custom Colors

Using the familiar Format Cells dialog box and your imagination, you can totally bypass Conditional Formatting to custom-color your numbers and text as you like.

In the above picture,

Read more ›

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

Tom’s Tutorials For Excel: Fuzzy Matching With Partial Strings For Sum or Average

Tom’s Tutorials For Excel: Fuzzy Matching With Partial Strings For Sum or Average

When you have a mish-mash of alphanumeric strings in a list, here’s how you can sum or average their corresponding numbers based on a partial string criterion.

The picture shows two examples with the criterion in cell B4.

Read more ›

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

Tom’s Tutorials For Excel: Enter Zip Codes and Social Security Numbers With Leading Zeros

Tom’s Tutorials For Excel: Enter Zip Codes and Social Security Numbers With Leading Zeros

When you enter numbers that start with a leading zero such as Social Security Numbers or United States zip codes, you must stop Excel from automatically hiding the leading zero, so your full number including the leading zero is displayed.

Read more ›

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

Tom’s Tutorials For Excel: Make Dates look Familiar When Combined With Words In A Formula

Tom’s Tutorials For Excel: Make Your Dates Readable When Combined With Text

When combining dates with words in a formula, dates don’t always look like dates in the result.

For example, the above picture shows a date in cell A1.

Read more ›

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

Tom’s Tutorials For Excel: COUNTIF and SUMIF Anything or Nothing

Tom’s Tutorials For Excel: COUNTIF and SUMIF Anything or Nothing

You can use the “” and “=” operators with worksheet functions for identifying the presence or absence of values in a cell.

In Picture #1, the formula in cell E4 is =COUNTIF(A2:A10,””).
The number 6 is returned because 6 items appear in range A2:A10.

Read more ›

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

Tom’s Tutorials for Excel: Delete Hyperlinks, Keep Their Text

Tom’s Tutorials for Excel: Delete Hyperlinks, Keep Their Text

At some point you have probably inherited or created a worksheet that contains active hyperlinks, when all you want in the cells is the hyperlink text itself. It’s annoying to unwittingly click onto a hyperlink cell that interrupts your work by suddenly taking you away from your worksheet.

Read more ›

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

Tom’s Tutorials for Excel: Separate numbers and letters from alphanumeric string

Tom’s Tutorials for Excel: Separate numbers and letters from alphanumeric string.
You sometimes need to extract only numbers, or only letters, from an alphanumeric string.

For example if you have this:
HTRDFR9745 and you only want 9745

or you have
65UYDKWHDHSDUK and you only want 65

or trickier yet you have a mix like this:
FJ7R9yW5PXQ and you only want 795

Or,

Read more ›

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