The SUBSTITUTE function in Excel is an awesome text function. It helps us manipulate and work with text strings in Excel.

As its name implies, the purpose of the SUBSTITUTE function is to replace text, or characters in a cell, with different text.

There are some very clever ways that we can put SUBSTITUTE to work.

This blog post explores 3 reasons why we should all love the SUBSTITUTE function in Excel.

If you prefer a video, you can find that below. Otherwise keep reading to see the formula examples.

[Read more…] about SUBSTITUTE Function in Excel – 3 Reasons to Love It

In this blog post we create a reverse FIND formula to extract text after the last occurrence of a character.

Many of you reading this may already be familiar with the FIND function. You would probably have used it with LEFT or MID to locate a delimiter character, and return text before, or after that character.

In this tutorial we want to extract text after the last occurrence of a character, so want to create a reverse find effect.

Watch the Video – Reverse Find Formula

[Read more…] about How to Create a Reverse FIND Formula

Nested IF formulas are extremely useful for complex decision making on a spreadsheet, but they can also be long, messy and convoluted.

This blog post explores 4 alternatives which are easier, faster and cleaner than the classic nested IF.

Watch the Video – Nested If Formula alternatives

[Read more…] about Nested IF Formulas – 4 Alternative Examples

The CHAR function in Excel returns a character specified by a code, from the character set of your computer.

In this blog post, we look at why you might want to use the CHAR function, and some examples of it in action in Excel.

The CHAR function is typically used to insert, or to find, a character which you ordinarily cannot type from your keyboard.

To use it you will need to find out what the code is for the character you want to use.

You can search online for a full list of these character sets so that you know each code (or at least the one you need at the given time). These character sets differ between the Windows and Mac computers, and are also dependent upon the language used.

[Read more…] about Excel CHAR Function with Examples