Excel Tips and Tricks #455 - Data Conversion

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:

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.

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.

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.

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:

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

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:

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.

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.