Format

Function converts your variables into appropriate (user defined) format.

Properties

Keywords

Format variables, Set Data Type, Variable Type, Change Type, Variable Type, Data Type

See Also

Mutate, Date_Parse_Num, Date_Parse_Exact

Video-tutorial

How to Format Excel Columns to Appropriate Data Types

The formatting function allows you to change the data type of the individual variables within the imported dataset. An example can be change of string type to a numeric. Examples of these data transformations are shown in the following figure.

In the example we use the table shown in the following figure. This table contains three columns with string data type. However, if we look at the content of individual cells, we see that they contain numeric, boolean and datum values. For further work with this dataset, we need to convert these columns to the correct data type.

For this purpose, click on the Format button on the ribbon toolbar tab Variables.

Selected function is displayed in the sidebar. In the bottom part are displayed function properties. Overall, there are two properties - Culture and Variables to Format.

If you want to change the data type of the selected variable from the table, click on the check-box in the list before the variable name. After this action, a combo-box will appear. In this combo-box, you can define the new data type to which you want to reformat the variable. You can choose from a number of data types. The most common types are Bool, Byte, DateTime, Double, Float, Char, Int and String.

An example of the Format function application displays the following figure. In this case, we have transformed the Date variable into the DateTime type, the variable Number we changed from String type to Double and finally variable Integer to String.

As is clear from the example, not all values of the Date variable have been converted. This is due to a different definition of the Date values. In the property Culture we have a defined value of "sk-SK". This property is by default based on your operating system settings. If you have the Culture defined as sk-SK, the system assumes that the date is entered in the format DD.MM.YYYY. Cells in which the values could not be converted were ignored and left blank.

For example, if you have stored date values in a different format, you can also convert them if you set the Culture property correctly. An example of a change is shown in the following figure. We changed the Culture to en-US. Subsequently, the program assumes that the date values are written in the MM.DD.YYYY format.

In the same way, you can proceed if, for example, you have a decimal separator defined as a comma instead of a dot.

In the case of dates, you can also format values in the form of "Long Date String" - where months are typed as the whole word, for example "May 1 2019". It is however necessary to use a correct Culture. For example, if you use German names in months, you need to set the Culture property to de-DE. For more complex Date and Time transformations, you can also use Date_Parse_Num and Date_Parse_Exact functions.