If you have a list of numbers that are a text numbers or a combination of text numbers with real numbers there is a technique I covered in this blog post to add them up. But if the range also contains text then the technique won’t work. There is the work around. The solutions below work in the subscription version of Excel. Check the comments section below for a solution for all versions.
Years back when I wrote my Excel book, I had to create an index for the book. I shared the file I used including the macro in this post. Recently I thought dynamic arrays could do much of the work for this.
I wrote an article years ago explaining how to use a related table to handle financial years in Excel Pivot Tables. You can read the article here. If you only want the months in financial year order you can just add an extra column to your table.
Let’s say you are getting inputs you can’t control and in some cases you get text and others you get numbers. You want the numbers, but you need to treat text as zero. Here’s the easy way to do that.
I wrote a blog post a few years back showing how to add up numbers formatted as text. If you have the subscription version of Excel you have another solution.
On a recent Webinar I was asked a question about an unusual date structure that was imported. The structure dd.mm.yy was not recognised by Excel as a date. Here is formula that fixes it.
Below is an example of the date issue.
The formula in cell B2 is
As you can see the dates in column A are left aligned. That is a clue that they are not recognised as dates in Excel. Dates are right aligned.
The SUBSTITUTE function replaces the full stop between the numerals with a / and makes it look like a date.
This isn’t sufficient as the SUBSTITUTE function will return text. The *1 at the end converts the text date in to a real date that Excel recognises.
Note: Power Query can also automatically fix dates like these when it imports data.
As per a comment from Rick Rothstein Excel MVP you can use the Text To Column feature to fix the dates in place.
Recently I found an interesting way to handle plurals in Jordan Goldmeier’s book on dashboards.
Did you know that you can format individual words and letters differently within an Excel cell or text box?
If you have a system that uses initials to identify people then being able to extract initials from a first name and last name combination can be handy. A formula can automate the process and there is also a quick, manual way to do it.
Sometimes when creating text you need to handle plurals correctly. The IF function makes it easy.
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.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
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.
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.
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.
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?
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.
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.
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.
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.
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.
There can be times when identifying the current or a specific column letter reference is useful. Check out the comments for a shorter formula – thanks Batuque.
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.
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?
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.
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.
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?
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.
The CONCATENATE function is redundant. You don’t to need to use it. There is a much quicker + easier way to join text together.