Dashpivot supports Excel-style text functions inside Default Tables and Prefilled Tables. These formulas are commonly used for:
Text extraction
Concatenation and combining text
Text replacement and substitution
Formatting and case conversion
Searching and matching text
Data cleaning and validation
CLEAN()
Purpose: Removes non-printable characters from text.
Formula to use:
=CLEAN(A1)
Example:
If A1 contains text with hidden characters
=CLEAN(A1) returns cleaned text without non-printable characters.
TRIM()
Purpose: Removes extra spaces before and after text (and reduces multiple spaces to a single space).
Formula to use:
=TRIM(A1)
Example:
If A1 = " Project Alpha "
=TRIM(A1) returns Project Alpha
LOWER()
Purpose: Converts text to lowercase.
Formula to use:
=LOWER(A1)
Example:
If A1 = "SAFETY REPORT"
=LOWER(A1) returns safety report
UPPER()
Purpose: Converts text to uppercase.
Formula to use:
=UPPER(A1)
Example:
If A1 = "site inspection"
=UPPER(A1) returns SITE INSPECTION
PROPER()
Purpose: Capitalises the first letter of each word.
Formula to use:
=PROPER(A1)
Example:
If A1 = "monthly safety meeting"
=PROPER(A1) returns Monthly Safety Meeting
TEXT()
Purpose: Converts a number into formatted text.
Formula to use:
=TEXT(A1,"0.00")
Example:
If A1 = 12
=TEXT(A1,"0.00") returns 12.00
Text extraction
LEFT()
Purpose: Extracts characters from the left of a text string.
Formula to use:
=LEFT(A1,B1)
Example:
If A1 = "Dashpivot"
and B1 = 4
=LEFT(A1,B1) returns Dash
RIGHT()
Purpose: Extracts characters from the right of a text string.
Formula to use:
=RIGHT(A1,B1)
Example:
If A1 = "Dashpivot"
and B1 = 5
=RIGHT(A1,B1) returns pivot
MID()
Purpose: Extracts text from a specific starting position.
Formula to use:
=MID(A1,B1,C1)
Example:
If A1 = "Inspection"
B1 = 4
C1 = 3
=MID(A1,B1,C1) returns pec
LEN()
Purpose: Returns the number of characters in text.
Formula to use:
=LEN(A1)
Example:
If A1 = "Safety"
=LEN(A1) returns 6
FIND()
Purpose: Returns the position of text within another text string (case-sensitive).
Formula to use:
=FIND(A1,B1)
Example:
If A1 = "Site"
and B1 = "Site Inspection"
=FIND(A1,B1) returns 1
SEARCH()
Purpose: Returns the position of text within another text string (not case-sensitive; supports wildcards).
Formula to use:
=SEARCH(A1,B1)
Example:
If A1 = "site"
and B1 = "Site Inspection"
=SEARCH(A1,B1) returns 1
SPLIT()
Purpose: Returns a word at a specified zero-based position (space-separated).
Formula to use:
=SPLIT(A1,B1)
Example:
If A1 = "Project Safety Report"
and B1 = 1
=SPLIT(A1,B1) returns Safety
(0 = first word, 1 = second word, etc.)
Text comparison
EXACT()
Purpose: Returns TRUE if text matches exactly (case-sensitive).
Formula to use:
=EXACT(A1,B1)
Example:
If A1 = "Site"
and B1 = "site"
=EXACT(A1,B1) returns FALSE
T()
Purpose: Returns text if the value is text; otherwise returns an empty string.
Formula to use:
=T(A1)
Example:
If A1 = "Inspection"
=T(A1) returns Inspection
If A1 = 100
=T(A1) returns an empty string.
Text replacement & repetition
REPLACE()
Purpose: Replaces part of a text string based on position.
Formula to use:
=REPLACE(A1,B1,C1,D1)
Example:
If A1 = "Dashpivot"
B1 = 5
C1 = 5
D1 = "board"
=REPLACE(A1,B1,C1,D1) returns Dashboard
SUBSTITUTE()
Purpose: Replaces matching text within a string.
Formula to use:
=SUBSTITUTE(A1,B1,C1)
Example:
If A1 = "Site Report Report"
B1 = "Report"
C1 = "Inspection"
=SUBSTITUTE(A1,B1,C1) returns Site Inspection Inspection
To replace a specific occurrence:
=SUBSTITUTE(A1,B1,C1,2)
REPT()
Purpose: Repeats text a specified number of times.
Formula to use:
=REPT(A1,B1)
Example:
If A1 = "*"
and B1 = 5
=REPT(A1,B1) returns *****
Text combination
CONCATENATE()
Purpose: Combines multiple text strings into one.
Formula to use:
=CONCATENATE(A1,B1)
Example:
If A1 = "Site"
and B1 = " Report"
=CONCATENATE(A1,B1) returns Site Report
Character code functions
CODE()
Purpose: Returns the ASCII code of the first character in text.
Formula to use:
=CODE(A1)
Example:
If A1 = "A"
=CODE(A1) returns 65
UNICODE()
Purpose: Returns the Unicode code point of the first character.
Formula to use:
=UNICODE(A1)
Example:
If A1 = "A"
=UNICODE(A1) returns 65
UNICHAR()
Purpose: Returns a character from a given Unicode number.
Formula to use:
=UNICHAR(A1)
Example:
If A1 = 65
=UNICHAR(A1) returns A