This Analytics Edge wizard is used to convert columns from one data type (text, number, or date) into another. It can also convert to a variant of the same type, like converting dates to first of the month. Typically used to force imported data into the correct type (such as dates with odd formats), it can also be used to convert dates and numbers into specific formats for reporting purposes.
[New in v10] Note that the wizard can be resized to show more fields and all of the options selected, plus you can change the settings for multiple fields by selecting them all before clicking Set or Clear.
Select Column – select a column in the list, shown with the column letter for position reference or optionally By Name. Depending on the type of column, you will be given additional options to convert the selected column into different text, number or date formats. Click Set to accept the selected conversion for that column.
When you select a column, different options will appear to the right:
Text to Text
Check at least one option and click the Set button. The options are self-explanatory:
- trim leading and trailing spaces
- remove new line characters
- remove non-printable characters
- truncate to a specific number of characters
- change to upper case
- change to lower case
Text to Number
Check at least one option and click the Set button.
Convert text to a number of a specific culture format, such as with a different decimal separator.
Extract numeric portion – i.e. just the digits from the text, ignoring the other characters.
Text to Date
Adjust the date format, select an option, and click the Set button.
Source date format – enter codes to match the format of the date in the column.
No adjustment – use the date provided.
Convert GMT to local time – assumes the date/time provided is in the GMT/UTC timezone. Converts to local time and timezone of the computer.
Convert local time to GMT – converts the local date/time to GMT/UTC.
Number to Text
Adjust the number format and culture, and click the Set button.
Format desired – enter the codes to match the desired numeric format.
Culture – select the culture for the numbers. This determines the decimal and thousands separators.
Number to Number
Select an option and click the Set button. The options are self-explanatory:
- Round to number of decimals
- Round to nearest ten (10, 20, 30, etc)
- Round to nearest hundred (100, 200, etc)
- Round to nearest thousand
- Round to nearest million
Number to Date
Select an option and click the Set button.
Treat number as an Excel serial number – this converts a numeric value into an Excel date. (1 = 1900-01-01)
Treat number as days since <date> – uses the number provided to add days to the date specified.
Treat number as <selection> – uses the number as an offset for the day or week, month or year.
Date to Text
Adjust the date format, select an option, and click the Set button.
No adjustment – use the date provided.
Convert GMT to local time – assumes the date/time provided is in the GMT/UTC timezone. Converts to local time and timezone of the computer.
Convert local time to GMT – converts the local date/time to GMT/UTC.
Date to Number
Select an option and click the Set button.
Convert to an Excel serial number – this converts from an Excel date to a number. (1900-01-01 = 1)
Treat number as <selection> – get the number as an offset for the day or week, month or year.
Days since <date> – counts the number of days since the date specified.
Date to Date
Select an option and click the Set button. The options are self-explanatory:
- Change to the 1st of the month
- Date only (time set to midnight)
- Time only (date set to 1900-01-00)
- Convert GMT/UTC to local time
- Convert local time to GMT/UTC