vert line

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.

 

Also go to:

Index help

Contents help