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,
…
Blog Archives
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.
…
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,
…
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,
…
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,
…
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.
…
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.
…
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;
…
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.
…
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,
…
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,
…
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,
…
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,
…
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.
…
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.
…
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.
…
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.
…
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.
…
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,
…
