The following text string functions are provided by PipeDream. There are examples of the string functions in the PipeDream Examples.
CHAR(n)
Returns the text character whose ASCII number is n.
CHAR(65)
returns "A"
.
CODE("text")
Returns the ASCII number of the first character of text.
CODE("A")
returns 65
.
EXACT("text1", "text2")
If text1 exactly matches text2, returns 1.
If text1 does not exactly match text2, returns 0.
EXACT("word","word")
returns 1.
EXACT("Word","word")
returns 0.
FORMULA_TEXT(cell_reference)
Returns as a string the formula in the cell referenced, so that it can be displayed in the worksheet. This is useful for annotating worksheets, and printing out the formula which created the results shown in the worksheet.
FIND("find_text", "in_text" {, start_n})
Looks for find_text in in_text optionally starting at start_n and returns the offset (starting at one) of the character in in_text at which find_text starts.
FIND("needle","haystack needle in")
returns 10
.
find_text cannot contain wildcards.
FIND is case-sensitive and does not ignore leading and trailing spaces.
JOIN("text1", "text2", "text3" …)
Joins strings together.
JOIN("back","gammon")
returns "backgammon"
.
LEFT("text", n)
Returns the leftmost n characters of text.
LEFT("rightwing",5)
returns "right"
.
LENGTH("text")
Returns the number of characters in text.
LENGTH("cat")
returns 3
.
LOWER("text")
Makes all characters in text lower-case.
LOWER( "Big Ben")
returns "big ben"
.
MID("text", start_n, chars_n)
Returns the middle chars_n characters of text starting at start_n.
MID("middle of the road",8,6)
returns "of the"
.
PROPER("text")
Makes the first character of each word in text upper-case.
PROPER("big ben")
returns "Big Ben
".
REPLACE("text", start_n, chars_n, "with_text")
Replaces the part of text starting at start_n for chars_n with with_text.
REPLACE("PipeDream 3 is brilliant",11,1,"4")
returns "PipeDream 4 is brilliant
".
REPT("text", n)
Repeats text n times.
REPT("yo",2)
returns "yoyo"
.
REVERSE("text")
Reverses text.
REVERSE("1 2 3 4")
returns "4 3 2 1
".
RIGHT("text", n)
Returns the rightmost n characters of text.
RIGHT("rightwing",4)
returns "wing"
.
STRING(n, decimals)
Converts n from a number to a text string with decimals decimal places
STRING(1234.567,1)
returns "1234.6"
.
See the description of the ROUND function in Miscellaneous Functions.
TEXT(number, string)
Returns a string of the number in the style specified. This allows formatted results other than those provided by PipeDream’s basic style system.
number may be a number or a date.
string is a format string in Fireworkz number format style.
Please refer to the Fireworkz User Guide for details.
TEXT(1234.567, "#,.###.,00 \€")
returns 1.234,57 \€
TEXT(1234.567, "g0.### \m")
returns 1.235 km
TRIM("text")
Removes all leading and trailing spaces from text and converts multiple spaces inside text into single spaces.
UPPER("text")
Makes all characters in text upper-case.
UPPER("Big Ben")
returns "BIG BEN"
.
VALUE("text")
Converts text to a number.
VALUE("56")
returns
56
.
VALUE("fred")
returns
0
.
VALUE("1.3.86")
returns
0
.