ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tips & Tricks

Excel Tips and Tricks #455 - Data Conversion

Author: Jonelle Johnson-Turner

Published: 26 Sep 2022

Exclusive content
Access to our exclusive resources is for specific groups of subscribers.

Hello all and welcome back to the Excel Tips and Tricks. This week, we have a General User post in which we are taking a closer look at how to convert data types in Excel.

Which types of data?

Tip #384 discusses the ‘Linked Data Types’ available in Excel, however, in this post we will be looking converting text, numerical, and logical data instead.

While there are various ways to convert data, an obvious but sometimes forgotten solution is to check if what you’re working with is available in the desired format directly from the source e.g., downloading a report as a .csv file. Assuming this is not possible, Excel has some handy functions available to easily convert your data to suit your needs.

How can we convert data types?

Text -> Number

The VALUE function can be used to convert a text string that represents a number into a number format however, Excel tends to do this automatically.

NUMBERVALUE also converts text to a number, but in a locale-independent way. When working with data from Europe for example where ‘.’ is used for a thousand (group) separator and ‘,’ for decimals, Excel usually stores these numbers as text. The function will give you results in your usual number format:

Excel screenshot

Text -> Date

As with other numbers, in many cases, Excel will convert text it recognises as a date automatically however, the DATEVALUE function is useful for when it doesn’t.

Excel Screenshot
There are limitations of the function, as the text does have to follow an accepted format, eg, “11 Dec 2021” is okay, whereas “Dec 11, 2021” is not.
Excel screenshot
To work around this, we can combine ‘Text to Columns’, Tip #392 (or the new “TEXTSPLIT” function discussed in our recent M365 Tech Features article) to split the day, month, and year, with DATEVALUE to quickly convert the data.
Excel screenshot

Number -> Text

Usually, we prefer to store values as numbers as these are more useful in subsequent calculation cells. However, text formats can be much easier on the eyes making the TEXT function very useful in some situations eg, displaying phone numbers:

Excel screenshot
or embedding a date in a heading (remembering that Excel stores dates and times as numbers, therefore the same conversion principle applies):
Excel screenshot

The TEXT function relies on format codes, which work on the same basis as those found in the “Format Cell” dialog box (via the “Number” section on the “Home” tab). This is a rare case where we have to concede the range of number formats is so complex we can’t really cover it here and defer to the TEXT function page on Microsoft Support which explores format codes in detail.

Logical -> Text

Certain functions are designed to give logical outputs or Booleans, usually TRUE/FALSE, which we may wish to convert into specific text more relevant to the user. Using the IF function to define the text desired will achieve this:

Excel screenshot

Logical -> Number

Similarly, we may want the outputs in a number format 1 vs 0 for use in future calculations. There are a variety of ways to achieve this including:

  1. Using the IF function as above, but with 1 and 0 as the values for TRUE/FALSE.
  2. Using the INT function to round to the nearest integer.
  3. Using “--” before the logical test.
Excel screenshot

As with most things in Excel, there are many more ways to achieve the same results. Which functions do you prefer when converting your data?

Author

Jonelle Johnson-Turner

Jonelle is a Business Consultant for the Travel Trade Consultancy helping travel businesses find solutions to complex regulatory, financial, and strategic problems.