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.
…
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.
…
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,
…
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: 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.
…
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,
…
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,
…
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.
…
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,
…
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.
…
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.
…
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”.
…
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,
…
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,
…
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,
…
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.
…
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.
…
San Francisco, California
1-925-310-4170
tom@atlaspm.com
