Adding up Text Numbers In Excel Another Technique

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.

Fix dd.mm.yy date format

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

=SUBSTITUTE(A2,".","/")*1

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.

Added 17/11/2021

As per a comment from Rick Rothstein Excel MVP you can use the Text To Column feature to fix the dates in place.

 

 

 

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.

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.

Download Text Function materials