String Functions

The following text string functions are provided by PipeDream. There are examples of the string functions in the PipeDream Examples.

CHAR

Syntax:

CHAR(n)

Returns the text character whose ASCII number is n.

Example:

CHAR(65)

returns "A".

CODE

Syntax:

CODE("text")

Returns the ASCII number of the first character of text.

Example:

CODE("A")

returns 65.

EXACT

Syntax:

EXACT("text1", "text2")

If text1 exactly matches text2, returns 1.

If text1 does not exactly match text2, returns 0.

Example:

EXACT("word","word") returns 1.

EXACT("Word","word") returns 0.

FORMULA_TEXT

Syntax:

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

Syntax:

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.

Example:

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

Syntax:

JOIN("text1", "text2", "text3" …)

Joins strings together.

Example:

JOIN("back","gammon")

returns "backgammon".

LEFT

Syntax:

LEFT("text", n)

Returns the leftmost n characters of text.

Example:

LEFT("rightwing",5)

returns "right".

LENGTH

Syntax:

LENGTH("text")

Returns the number of characters in text.

Example:

LENGTH("cat")

returns 3.

LOWER

Syntax:

LOWER("text")

Makes all characters in text lower-case.

Example:

LOWER( "Big Ben")

returns "big ben".

MID

Syntax:

MID("text", start_n, chars_n)

Returns the middle chars_n characters of text starting at start_n.

Example:

MID("middle of the road",8,6)

returns "of the".

PROPER

Syntax:

PROPER("text")

Makes the first character of each word in text upper-case.

Example:

PROPER("big ben")

returns "Big Ben".

REPLACE

Syntax:

REPLACE("text", start_n, chars_n, "with_text")

Replaces the part of text starting at start_n for chars_n with with_text.

Example:

REPLACE("PipeDream 3 is brilliant",11,1,"4")

returns "PipeDream 4 is brilliant".

REPT

Syntax:

REPT("text", n)

Repeats text n times.

Example:

REPT("yo",2)

returns "yoyo".

REVERSE

Syntax:

REVERSE("text")

Reverses text.

Example:

REVERSE("1 2 3 4")

returns "4 3 2 1".

Syntax:

RIGHT("text", n)

Returns the rightmost n characters of text.

Example:

RIGHT("rightwing",4)

returns "wing".

STRING

Syntax:

STRING(n, decimals)

Converts n from a number to a text string with decimals decimal places

Example:

STRING(1234.567,1)

returns "1234.6".

See the description of the ROUND function in Miscellaneous Functions.

TEXT

Syntax:

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.

Example:

TEXT(1234.567, "#,.###.,00 \€") returns 1.234,57 \€

TEXT(1234.567, "g0.### \m") returns 1.235 km

TRIM

Syntax:

TRIM("text")

Removes all leading and trailing spaces from text and converts multiple spaces inside text into single spaces.

UPPER

Syntax:

UPPER("text")

Makes all characters in text upper-case.

Example:

UPPER("Big Ben")

returns "BIG BEN".

VALUE

Syntax:

VALUE("text")

Converts text to a number.

Examples:

VALUE("56") returns 56.

VALUE("fred") returns 0.

VALUE("1.3.86") returns 0.