|
![]() |
![]() |
CHAR or @CHAR Returns the ASCII character for the given number. CHAR(num) num any integer representing an ASCII character The CHAR function will convert the integer num to its corresponding ASCII character. The ASCII code for a given character can be found with the CODE function. See "CODE or @CODE" . =CHAR(68) -> D @CHAR(162) -> ¢ CLEANor @CLEAN Removes all control characters from a string. CLEAN(string) string any string The CLEAN function will remove all control characters from the string string. This can be useful for data that has been imported to Mesa from another source. CODEor @CODE Returns the ASCII code for the first character in a string. CODE(string) string any string (only the first character will be used) The CODE function returns the ASCII code for the first character in the string string. The character for a given code can be found with the CHAR function. See "CHAR or @CHAR". =CODE("A") -> 65 @CODE("{") -> 123 =CODE("Hello") -> 72 : This formula is returning the ASCII code for "H" EXACTor @EXACT Compares two strings to determine if they are exact matches. EXACT(str1, str2) str1 the first string to be compared str2 the second string to be compared The EXACT function compares str1 and str2, and returns 1 if the strings match exactly. It returns 0 otherwise. The EXACT function is case sensitive. When you use = to match strings, the comparison ignores case. For example, "Upper" = "upper" is true, but EXACT("Upper", "upper") is false. =EXACT("Daytime","Daytyme") -> 0 (false) A1="hello" and B1="hello": =EXACT(A1,B1) -> 1 (true) FINDor @FIND Finds the location of a substring within a string. FIND(substr, string, offset) substr the substring to be found string the string to be searched offset the location in the string to begin the search The FIND function returns the location of the sub-string substr in the string string. It starts searching for substroffset characters from the left of string. It returns ERR if the string is not found. =FIND("hampton","Northampton",1) -> 4 =FIND("hampton","Northampton",6) -> !ERR (because it starts searching at the 6th character) A1: "The third word in this sentence." =FIND("word",A1,1) -> 10 FIXEDor @STRING Rounds a number to a specified number of decimal places and converts it to a string. FIXED(num, prec) num any number prec the number of places of precision The FIXED function rounds the number num to a precision of prec decimal places, then converts it to a string. If prec is negative, num will be rounded off to the left of the decimal point. B3: 5251.769585987 =FIXED(B3,6) = 5251.769586 =FIXED(3/43,2)&" is now a string" -> 0.07 is now a string : This formula evaluates 3/43, then converts it to a string with 2 decimal places of precision and concatenates it with the text " is now a string". LEFTor @LEFT Returns a substring from the left of a string. LEFT(string, len) string any string len the length of the substring The LEFT function returns a string containing the first len characters from the left of the string string. =LEFT("The first twelve characters",12) -> The first tw =LEFT("ABCDEFG",4) -> ABCD LEN, LENGTH, @LENGTH or @LEN Returns the length of a string. LENGTH(string) string any string The LENGTH function returns the number of characters in the string string. LENGTH("10% discount on COD") = 19 A1: "A short string " B1: "sentence" =LEN(A1) -> 15 =LENGTH(A1&B1) -> 23 LOWERor @LOWER Converts a string to lower case. LOWER(string) string any string The LOWER function converts all the characters in the string string to lower case, regardless of their original case. =LOWER("Bill Johnson") -> bill johnson MIDor @MID Returns a substring from the middle of a string. MID(string, start, len) string any string start the starting character of the substring len the length of the substring The MID function returns a string of len characters from the string string, starting start characters from the first character of string. = MID("Have a great day!",7,5) = great A1: "Robert Quimby Douglas", =MID(A1,7,6) -> Quimby NEXTor @NEXT Returns the next logical value for a given value NEXT(value[,inc]) value a number, string, or cell reference inc the [optional] number by which to increment the value The NEXT function returns the next logical value after value. The value is incremented by 1 unless an optional increment inc is specified. For example, if value is "Mon", then NEXT(value) returns "Tue". Values for months, days, and quarters are built in. If value is a number, NEXT adds 1 or inc to the number. For quarterly information, it will increment the quarter. When the quarter reaches 4, the next quarter is Q1 of the following year. For example, NEXT("Q4 '92") is "Q1 '93". =NEXT("Q4 '92") -> Q1 '93 =NEXT("January") -> February =NEXT("Region 1") -> Region 2 PROPERor @PROPER Converts a string to Proper Case. PROPER(string) string any string The PROPER function capitalizes the first letter of every word in the string string and converts all remaining characters to lower case. =PROPER("MS. JANET KRAFT") -> Ms. Janet Kraft =PROPER("all entries posted before 7/2") -> All Entries Posted Before 7/2 REPEAT, REPT, or @REPEAT Repeats a string the specified number of times. REPEAT(string, num) string any string num the number of times to repeat the string This function returns the string string repeated num times. The string is placed back-to-back with itself, with no white space. =REPEAT("X",10) -> XXXXXXXXXX =REPT("over and ",4) -> over and over and over and over and REPLACEor @REPLACE Replaces a specified number of characters in a string with another string. REPLACE(string, offset, num, newstr) string any string offset the position to begin the insertion num the number of characters to replace newstr the string with which to replace the specified characters The REPLACE() function replaces num characters in the string string with the string newstr, starting offset characters from the first character of the original string. The string newstr does not need to be the same length as the number of characters being replaced. =REPLACE("The cost is $450, COD",13,3,"2,500") -> The cost is $2,500, COD =REPLACE("Robert J. Stevenson",7,1,"L") -> Robert L. Stevenson RIGHTor @RIGHT Returns a substring from the right of a string. RIGHT(string, len) string any string len the length of the substring The RIGHT function returns a string containing the first len characters from the right of the string string. =RIGHT("The last ten characters",10) -> characters =RIGHT("Ida M. Brown",5) -> Brown TRIMor @TRIM Removes extra white space from a string TRIM(string) string any string This function removes all leading and trailing spaces from the string string and converts multiple consecutive spaces within the string to single spaces. A3: Account # 3040302 40303 =TRIM(A3) -> Account # 3040302 40303 =TRIM(" Anything at all.") -> Anything at all. UPPERor @UPPER Converts a string to UPPER case. UPPER(string) string any string The UPPER function converts all the characters in the string string to upper case, regardless of their original case. =UPPER("Uppers only") -> UPPERS ONLY =UPPER(MID("robert c. smith",8,2)) -> C. : This formula uses MID() to return the 8th and 9th characters from the string "Robert C. Smith". The UPPER() function then capitalizes this result. VALUE Returns the number value of a string. VALUE(string) string any string This function converts the string string into a number. If the string begins with a number but contains non-numbers, VALUE() returns as much as it can. If string begins with a letter, it returns 0. =VALUE("3.14159") = 3.14159 =VALUE("1200") + 2000 = 3200 =VALUE("320/104") = 320 @VALUE Returns the number value of a string. @VALUE(string) string any string This function converts the string string into a number. If the string contains characters that are non-numbers, it returns an error. @VALUE("3.14159") -> 3.14159 @VALUE("1200") + 2000 -> 3200 @VALUE("320/104") -> !ERR VALUE() and @VALUE() are different in that if the argument to VALUE() cannot be completely converted to a number, it returns as much of the number as possible. @VALUE() returns an error. |
||
|
||