>>>

5. Useful Functions for Text Manipulation

CHAR.........character ANSI code: CHAR(65) retuns "A"

CLEAN........non-printable characters removed: CLEAN("abc"&CHAR(7)&"def") retuns "abcdef"

CODE.........first character ANSI code: CODE("ABC") returns 65

CONCATENATE..concatenate text: CONCATENATE(A1," ",B1) returns "one two":

	A	B
1	one	two

DOLLAR.......dollar formatting $0,000.00: DOLLAR(99.888) returns "$99.89"

EXACT........TRUE if two strings match, FALSE otherwise: EXACT(A1,"one") returns TRUE

	A
1	one

FIND.........substring position in a string: FIND("two",A1) returns 5

	A
1	one two three

FIXED........number rounded to fixed decimal places: FIXED(1234.567,1) returns 1234.6

LEFT.........leftmost characters of a string: LEFT(A1,3) returns "one"

	A
1	one two three

LEN..........string size: LEN("some text") returns 9

LOWER........text to lower case: LOWER("ABC") returns "abc"

MID..........substring of text: MID("some text",3,2) returns "me"

PROPER.......first letters of words to capital: PROPER("some text") returns "Some Text"

REPLACE......replace characters: REPLACE("one two",5,3,"three") returns "one three"

REPT.........repeat text: REPT("a",7) returns "aaaaaaa"

RIGHT........rightmost part of text: RIGHT(A1,5) returns "three"

	A
1	one two three

SEARCH.......SEARCH(what,within): SEARCH(A1,B1) returns 5:

	A	B
1	one	two one

SUBSTITUTE...replace substring: SUBSTITUTE("old text","old","new") returns "new text"

T("text")....returns "text"

T(123).......returns empty string if argument is a number.

TEXT.........returns number with formatting: TEXT(2.715,"$0.00") returns "$2.72"
                                             TEXT(37324,"dd/mm/yyyy") returns "09/03/2002"
                                             TEXT("01/01/1997","mm-dd-yyyy") returns 01-01-1997
                                             TEXT(0.2,"0%") returns "20%"

TRIM.........removes front and back spaces: TRIM(" text ") returns "text"

UPPER........text to UPPER CASE: UPPER("abc") returns "ABC"

VALUE........convert text to number: VALUE("1,000") returns 1000

<<<

Index

>>>