String Functions

The following string functions are provided to operate upon text strings.

CHAR

CHAR(n)

Returns the text character whose ASCII number is n.

For example,

CHAR(65)

returns "A".

CODE

CODE("text")

Returns the ASCII number of the first character of text.

For example,

CODE("A")

returns 65.

EXACT

EXACT("text1", "text2")

If text1 exactly matches text2, returns 1.

If text1 does not exactly match text2, returns 0.

For example:

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

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

FORMULA_TEXT

FORMULA_TEXT(slot_ref)

Returns as a string the formula in the slot 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("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.

For 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

JOIN("text1", "text2", "text3", "text4", "text5", ...)

Joins strings together.

For example,

JOIN("back","gammon")

returns "backgammon".

LEFT

LEFT("text", n)

Returns the leftmost n characters of text.

For example,

LEFT("rightwing",5)

returns "right".

LENGTH

LENGTH("text")

Returns the number of characters in text.

For example,

LENGTH("cat")

returns 3.

LOWER

LOWER("text")

Makes all characters in text lower-case.

For example,

LOWER( "Big Ben")

returns "big ben".

MID

MID("text", start_n, chars_n)

Returns the middle chars_n characters of text starting at start_n.

For example,

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

returns "of the".

PROPER

PROPER("text")

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

For example,

PROPER("big ben")

returns "Big Ben".

REPLACE

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

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

For example,

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

returns "PipeDream 4 is brilliant".

REPT

REPT("text", n)

Repeats text n times.

For example,

REPT("yo",2)

returns "yoyo".

REVERSE

REVERSE("text")

Reverses text.

For example,

REVERSE("1 2 3 4")

returns "4 3 2 1".

RIGHT

RIGHT("text", n)

Returns the rightmost n characters of text.

For example,

RIGHT("rightwing",4)

returns "wing".

STRING

STRING(n, decimals)

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

For example,

STRING(1234.567,1)

returns "1234.6".

See the description of the ROUND function in Miscellaneous Functions.

TEXT

TEXT(number, string)

Returns a string of the number in the style specified. This allows formatted results other than those provided by PipeDream's 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 Spreadsheet Guide for details.

For example,

TEXT(1234.567, "€#,###.00") returns €1,234.57

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

TRIM

TRIM("text")

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

UPPER

UPPER("text")

Makes all characters in text upper-case.

For example,

UPPER("Big Ben")

returns "BIG BEN".

VALUE

VALUE("text")

Converts text to a number.

Examples:

VALUE("56") returns 56.

VALUE("fred") returns 0.

VALUE("1.3.86") returns 0.