Posted on Monday, May 21, 2018

Free Excel Webinar Recording – Text Functions explained and demonstrated

How to text safely in Excel

My free Excel webinar for May 2018 covered Text functions. Download the materials using the button below and watch the video.

You know how well Excel handles numbers, but not everyone knows that Excel has built-in functions and features to work with text as well. This session covers Excel’s text functions and features, in it you will learn

the different techniques to split text
techniques to extract text from text
how to easily join text
techniques for tweaking text for dates, numbers, upper and lower case
the formulas for extracting sheet and file names
two new Excel 2016 functions for combining text from ranges

As always, I will be sharing a few other tips during the session.

Download Text Function materials

Posted on Tuesday, April 17, 2018

PivotTable Combinations Trick

How to get a list of combinations

I did a recent post on using Power Query to create an all combinations list. I found another technique on chandoo.org to create that list using a PivotTable.

Posted on Tuesday, September 26, 2017

Converting Text Numbers and Reversing Signs in Excel

Paste Special to the rescue

Converting multiple text numbers into real numbers or reversing the sign on multiple numbers is easy in Excel if you know how to use Paste Special.

Posted on Tuesday, December 27, 2016

Converting a Tricky Date Format

I had a question on another post on how to convert Nov 21, 2014 into a date Excel recognises. The solution involves six functions working together.

Posted on Tuesday, December 20, 2016

Converting a number to a text number in Excel

An easy formula

If you need to convert a number into a text number within a formula there are a couple of ways to achieve this, but one way is a lot easier.

Posted on Tuesday, October 4, 2016

The Handy Functions in Excel – LEFT and RIGHT

Simple Defaults

The LEFT and RIGHT functions are great for extracting leading or trailing characters from a text string. Did you know their default setting is handy too?

Posted on Tuesday, August 11, 2015

Using a Number as Text in Excel Made Easy

When using numbers in text strings you usually need to format them. You typically need to use the comma format and handle decimals. There is a function that can simplify this task.

Posted on Tuesday, May 19, 2015

Generating Random Text Entries in Excel

Create random first and last name combinations

In my previous blog post I discussed generating random numbers in Excel. What about generating random text? E.g. generating random names for testing or training purposes.

Posted on Tuesday, April 21, 2015

Excel Flowchart Technique

Excel has great charts to help you visual your numbers, but it can also allow you to use flowcharts to help visualise numbers in a different way and help explain relationships between numbers and how they are formulated.

Posted on Tuesday, December 16, 2014

Cell Line Breaks Via a Formula

When creating a cell that joins text together you may want to include a line break between parts of the text. A function and a format combined can provide that.

Posted on Tuesday, December 2, 2014

Two Excel Functions to Format Numbers as Text

Using numbers in automated text sentences can be frustrating. Typically you don’t want to display decimals, but you do want to use the comma format.

Posted on Tuesday, November 18, 2014

Extracting the Column Letter in Excel

There can be times when identifying the current or a specific column letter reference is useful.

Posted on Wednesday, January 29, 2014

Insert a Note in an Excel Formula

If you want to place a note inside an Excel formula you can use an old function that is, in most cases, redundant. The N function was used in early spreadsheets, but is hardly ever used in modern formula.

Posted on Wednesday, October 16, 2013

Counting Characters in Excel

Another useful SUMPRODUCT technique

If you need to know how many characters are in a cell then use the LEN function. What if you wanted to know how many characters were in a range?

Posted on Tuesday, June 11, 2013

Quick as a Flash with Excel 2013

A new feature that's not perfect yet

Excel’s brand new Flash Fill feature can be a time saver, but it’s not fool proof. Flash Fill is a new feature in Excel 2013.

Posted on Thursday, March 14, 2013

How to get to first place in Excel

Or second and third

If you need to convert a number to its ordinal so that 1 = 1st and 4 = 4th then there is no built function to do it, but you can write a formula to do it.

Posted on Wednesday, January 16, 2013

Excel’s shorthand shortcut

Save typing

Excel has a feature that can save you a lot of typing, it’s called AutoCorrect. Yes it’s the same system that fixes your spelling mistakes. Well did you know it has two handy features?

Posted on Friday, December 7, 2012

Creating Dynamic Text Box entries in Excel

Flexible text

Text boxes are flexible because you can place them anywhere on your spreadsheet.

You can link a text box to the contents of a cell.

Posted on Thursday, November 22, 2012

Joining Text in Excel the quick and easy way

BAN CONCATENATE!

The CONCATENATE function is redundant. You don’t to need to use it. There is a much quicker + easier way to join text together.

A4 Accounting

Helping you Excel Yourself with spreadsheets

Tag Archives: text