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

…

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

…

**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.

…

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

…

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

…

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

…

**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.

…

**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.

…

**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;

…

**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.

…

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

…

- San Francisco, California
- 1-925-310-4170
- tom@atlaspm.com