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:
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.
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:
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:
- Using the IF function as above, but with 1 and 0 as the values for TRUE/FALSE.
- Using the INT function to round to the nearest integer.
- 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?
Jonelle is a Business Consultant for the Travel Trade Consultancy helping travel businesses find solutions to complex regulatory, financial, and strategic problems.
- Excel Tips and Tricks #462 - New text manipulation functions - TEXTBEFORE and TEXTAFTER
- Excel Tips and Tricks #461 – Things Google Sheets (still) does better than Excel
- Excel Tips and Tricks #460 - Revisiting hidden worksheets
- Excel Tips and Tricks #459 - Examples of using LAMBDA() in Excel
- Excel Tips and Tricks #458 - Revisiting Line breaks in Excel