Transform with Excel Formulas
One of the primary and powerful means of achieving data transformations in Visitran is by adding a new column to a table/model and expressing what transformation is needed by means of an Excel formula. Visitran supports a comprehensive set of 100+ Excel formulas to help you with data transformations. These formulas are organized by their use case in data transformation workflows, making it easy to find the right formula for your task.
Quick Access
Check out the ⭐ Common & Essential section in the sidebar for quick access to the most frequently used formulas.
Formula Categories
Data Cleaning & Formatting
Transform and standardize your data for consistency and quality.
- CLEAN - Removes non-printable characters
- TRIM - Removes extra spaces from text
- UPPER - Converts text to uppercase
- LOWER - Converts text to lowercase
- PROPER - Capitalizes the first letter in each word
- FIXED - Formats a number as text with fixed decimals
- REPT - Repeats text a given number of times
- SUBSTITUTE - Substitutes new text for old text
String Operations & Parsing
Extract, combine, and manipulate text fields in your data.
- CONCAT - Joins text items into one text item
- CONCATENATE - Joins several text strings into one
- LEFT - Returns characters from start of text string
- RIGHT - Returns characters from end of text string
- MID - Returns specific characters from a text string
- LEN - Returns the number of characters in a text string
- FIND - Finds text within another text string (case-sensitive)
- SEARCH - Finds text within another text string (not case-sensitive)
- EXACT - Checks if two text values are identical
- CODE - Returns the numeric code for the first character
- DIFFERENCE - Compares two text strings
Data Type Conversion
Convert between different data types seamlessly.
- CAST - Converts a value to a specified type
- NUMBERVALUE - Converts text to number in locale-independent way
- N - Returns a value converted to a number
- INT - Rounds a number down to the nearest integer
Date & Time Transformations
Work with temporal data and time series operations.
- DATE - Returns the serial number of a date
- DAY - Returns the day of a date
- DAYS - Returns the number of days between two dates
- EDATE - Returns a date a specified number of months before/after
- HOUR - Returns the hour component of a time
- MINUTE - Returns the minutes component of a time
- MONTH - Returns the month component of a date
- NOW - Returns the current date and time
- SECOND - Returns the seconds component of a time
- TIME - Returns the serial number of a time
- TODAY - Returns today's date
- WEEKDAY - Returns the day of the week
- WEEKNUM - Returns the week number of the year
- YEAR - Returns the year component of a date
- ISOWEEKNUM - Returns the ISO week number
Mathematical Transformations
Perform numerical calculations and transformations on your data.
- ABS - Returns the absolute value of a number
- CEILING - Rounds a number up to the nearest multiple
- DELTA - Tests whether two values are equal
- EVEN - Rounds a number up to the nearest even integer
- EXP - Returns e raised to the power of a number
- FLOOR - Rounds a number down to the nearest multiple
- GESTEP - Tests whether a number is greater than a threshold
- LN - Returns the natural logarithm of a number
- LOG - Returns the logarithm of a number to a specified base
- LOG10 - Returns the base-10 logarithm of a number
- MOD - Returns the remainder after division
- ODD - Rounds a number up to the nearest odd integer
- PI - Returns the value of pi
- POWER - Returns a number raised to a power
- QUOTIENT - Returns the integer portion of a division
- ROUND - Rounds a number to a specified number of digits
- ROUNDDOWN - Rounds a number down toward zero
- ROUNDUP - Rounds a number up away from zero
- SIGN - Returns the sign of a number
- SQRT - Returns the square root of a number
- SQRTPI - Returns the square root of (number * pi)
Statistical & Aggregations
Summarize and analyze your data with statistical functions.
- AVERAGE - Returns the average of its arguments
- COALESCE - Returns first non-null value
- MAX - Returns the maximum value in a list of arguments
- MIN - Returns the minimum value in a list of arguments
- PRODUCT - Multiplies all numbers given as arguments
- SUM - Adds its arguments
- SUMSQ - Returns the sum of the squares of the arguments
Conditional Logic & Business Rules
Implement business logic and conditional transformations.
- AND - Returns TRUE if all arguments are TRUE
- BETWEEN - Checks if a value is between two bounds
- CHOOSE - Chooses a value from a list of values
- IF - Specifies a logical test to perform
- IFNA - Returns a specified value if the expression is #N/A
- IFS - Checks multiple conditions
- ISIN - Checks if a value exists in a list
- NOT - Reverses the logic of its argument
- NOTIN - Checks if a value does not exist in a list
- OR - Returns TRUE if any argument is TRUE
- SWITCH - Evaluates an expression against a list of values
- XOR - Returns a logical exclusive OR
Data Validation & Quality Checks
Validate data quality and check for specific conditions.
- DUPLICATE - Checks for duplicate values
- FALSE - Returns the logical value FALSE
- ISBLANK - Returns TRUE if value is blank
- ISEVEN - Returns TRUE if number is even
- ISNA - Returns TRUE if value is #N/A
- ISNUMBER - Returns TRUE if value is a number
- ISODD - Returns TRUE if number is odd
- ISTEXT - Returns TRUE if value is text
- TRUE - Returns the logical value TRUE
Trigonometric & Scientific
Advanced mathematical functions for scientific and engineering transformations.
- ACOS - Returns the arccosine of a number
- ASIN - Returns the arcsine of a number
- ATAN - Returns the arctangent of a number
- ATAN2 - Returns the arctangent from x- and y-coordinates
- COS - Returns the cosine of a number
- COT - Returns the cotangent of an angle
- DEGREES - Converts radians to degrees
- RADIANS - Converts degrees to radians
- SIN - Returns the sine of an angle
- TAN - Returns the tangent of an angle
Bitwise Operations
Low-level bit manipulation for specialized data operations.