Dashpivot offers formulas that allow you to manipulate and transform text data. They help in data cleaning, formatting, concetenation, extraction, and text analysis. These are the main uses of text formulas:
Text Extraction
Text Concatenation
Text Replacement/Substitution
Text Formatting
Searching Text
Data Cleaning
Splitting Text
Combining Text
Text Analysis
Make use of the text formulas in Dashpivot to streamline your forms and make processes much easier to track.
Function ID | Description | Syntax | Sample | Results | Notes |
CLEAN | Returns text that has been "cleaned" of line breaks and other non-printable characters. | CLEAN("Text") | =CLEAN(A1) *A1 - text field Given that, A1 = “Hello, World” | Returns text, Hello, World
| This function removes the first 32 non-printable ASCII characters (codes 0 through 31) from the text. However, it does not remove some other Unicode characters. |
CODE | Returns a numeric code for the first character in a text string. | CODE("Text") | =CODE(A1) *A1 - text field Given that, A1 = “Alpine” | Returns, 65 (ASCII code for the character 'A') | Only returns the code for the first character in the text string. |
CONCATENATE | Combines several text strings into one string. | CONCATENATE("Text1", ..., "Text30") | Ex. 1 =CONCATENATE(A1, “ “, B1) *A1 & B1 - text fields Given that, A1 = “Hello” B1 = “World” __________ Ex. 2 =CONCATENATE (C1, “ and ”, D1, “ and ”, E1) *C1, D1, & E1 - text fields Given that C1 = “Uno” D1 = “Dos” E1 = “Tres” | Ex. 1 Returns text, Hello World _______ Ex. 2 Returns text, Uno and Dos and Tres |
|
EXACT | Returns TRUE if both text strings are exactly the same. | EXACT(Text, Text) | Ex. 1 =EXACT(A1, B1) *A1 & B1 - text fields Given that, A1 = “Dash” B1 = “Dash” __________ Ex. 2 =EXACT(C1, D1) *C1 & D1 - text fields Given that, C1 = “Pivot” D1 = “pivot” | Ex. 1 Returns, True (because both text strings are identical) _______ Ex. 2 Returns, False (because “Pivot” and “pivot” differ in case) | This function is useful for precise matching of text strings, such as password verification, case-sensitive data comparisons, or where a slight difference in text matters |
FIND | Returns the location of one text string inside another. | FIND( "Text1", "Text2"[, Number])
where: “Text1” = the text you want to find “Text2” = the text string in which you want to search more “Text1” Number = the position to tart the search | =FIND(A1, B1, Number) *A1 & B1 - text fields Given that, A1 = World B1 = Hello World Number = 0 | Returns, 7 (because “World” starts at the 7th character) | If Number is omitted, the search begins at the first character. |
LEFT | Extracts a given number of characters from the left side of a text string. | LEFT("Text", Number) | =LEFT(A1, Number) *A1 - text field Given that, A1 = “Quickstart” Number = 5 | Returns text, Quick (extracting the first 5 letters from the text) | If Number > length of “Text”, LEFT will return the entire text.
If Number < length of “Text”, LEFT will return a #VALUE! error. |
LEN | Returns length of a given text. | LEN("Text") | =LEN(A1) *A1 - text field Given that, A1 = “Hello World” | Returns, 11 (because there are 11 characters including the space) | Counts all characters including spaces (within the texts only; does not count spaces before and after texts) and punctuation.
|
LOWER | Returns text converted to lowercase. | LOWER(Text) | =LOWER(A1) *A1 - text field Given that, A1 = “HELLO World!” | Returns text, hello world! | It does not affect any characters that are already in lowercase or any non-letter characters. |
MID | Returns substring of a given length starting from Start_position. | MID(Text, Start_position, Length) | =MID(A1, Start_position, Length) *A1 - text field Given that, A1 = “Hello World!” Start_position = 7 Length = 5 | Returns text, World
(because the 7th letter is W (1st letter) plus followed by the next consecutive 4 letters. |
|
PROPER | Capitalizes words given text string. | PROPER("Text") | = PROPER(A1) *A1 - text field Given that, A1 = “hELLo, woRLd, 1 daY” | Returns text, Hello, World, 1 Day | This function treats any non-letter character (space, punctuation marks, numbers) as a word separator. |
REPLACE | Replaces substring of a text of a given length that starts at given position. | REPLACE(Text, Start_position, Length, New_text) | =REPLACE(A1, Start_position, Length, New_text) *A1 - text field Given that, A1 = “Hello World Nice” Start_position = 7 Length = 5 New_text = “Dash” | Returns text, Hello Dash Nice |
|
REPT | Repeats text a given number of times. | REPT("Text", Number) | =REPT(A1, Number) *A1 - text field Given that, A1 = “Hello” Number = 3 | Returns text, HelloHelloHello |
|
RIGHT | Extracts a given number of characters from the right side of a text string. | RIGHT("Text", Number) | =RIGHT(A1, Number) *A1 - text field Given that, A1 = “Quickstart” Number = 5 | Returns text, start | If Number > length of “Text”, RIGHT will return the entire text.
If Number < length of “Text”, RIGHT will return a #VALUE! error. |
SEARCH | Returns the location of one text string inside another. (Allows the use of wildcards.) | SEARCH( "Text1", "Text2"[, Number]) where: “Text1” = the substring you want to find “Text2” = the text string in which you want to search more “Text1” Number = the position to tart the search | =SEARCH(A1, A2, Number) *A1 - text field Given that, A1 = “World” A2 = “Hello World” Number = 0 | Returns text, 7 (because “World” starts at the 7th character) | If Number is omitted, the search begins at the first character. |
SPLIT | Divides the provided text using the space character as a separator and returns the substring at the zero-based position specified by the second argument. | SPLIT(Text, Index) | =SPLIT(A1, Index) *A1 - text field Given that, A1 = “The quick brown fox” Index = 3 | Returns text, brown (Because “brown” is the third word of the text separated by the space character) |
|
SUBSTITUTE | Returns string where occurrences of Old_text are replaced by New_text. Replaces only specific occurrence if last parameter is provided. | SUBSTITUTE(Text, Old_text, New_text, [Occurrence]) | =SUBSTITUTE(A1, Old_text, New_text, Occurence) *A1 - text field Given that, A1 = “apple orange apple” Old_text = “apple” Next_text = “banana” Occurence = 2 | Returns text, apple orange banana (Because only the second occurrence is indicated to change from “apple” to “banana”) |
|
T | Returns text if given value is text, empty string otherwise. | T(Value) | Ex. 1 =T(A1) *A1 - text field Given that, A1 = “Dashpivot” ________ Ex. 2 =T(A2) *A2 - text field Given that, A2 = “123” ________ Ex. 3 =T(A3) *A3 - text field Given that, A3 = “Dashpivot 123” | Ex. 1 Returns, Dashpivot (Because A1 contains a text)
_______ Ex. 2 Returns, “ “ (Because A2 contains a number)
_______ Ex. 3 Returns, Dashpivot 123
(Because A3 is considered as a text) |
|
TEXT | Converts a number into text according to a given format. | TEXT(Number, Format) | =TEXT(A1, "0.00") *A1 - number field Given that, A1 = “1234.5678” | Returns, 1234.57 (rounding to two decimal places) |
|
TRIM | Strips extra spaces from text. | TRIM("Text") | =TRIM(A1) *A1 - text field Given that, A1 = “ abcd “ | Returns text, abcd | Trims extra spaces before and after the sentence. Spaces in between are considered. |
UNICHAR | Returns the character created by using provided code point. | UNICHAR(Number) | =UNICHAR(A1) *A1 - number field Given that, A1 = “65” | Returns, A
(the character for the Unicode value 65 is the letter "A") | The numeric value passed to UNICHAR must be a valid Unicode code point (ranging from 1 to 1,114,111). If the number is outside this range, UNICHAR will return a #VALUE! error. |
UNICODE | Returns the Unicode code point of a first character of a text. | UNICODE(Text) | =UNICODE(A1) *A1 - text field Given that, A1 = “A” | Returns, 65 (the unicode for the letter A is “65”) |
|
UPPER | Returns text converted to uppercase. | UPPER(Text) | =UPPER(A1) *A1 - text field Given that, A1 = “Hello World” | Returns, HELLO WORLD |
|