Don’t see a problem here except the semi colon in your example. Remove it and use a comma instead.

I tried using commas and It works, but I found another problem.

If I use =TEXT(MONTH(Created),"mmmm") I see January instead of April. The right function  is =TEXT([Created],"mmmm").

It’s OK for date values. What other values are expected from calculated  fields? Can somebody point to a resource?

The online help has a list of formulas/functions you can use. They’re also very similar to the Excel formulas so you can refer to that help as well for examples as the SharePoint help doesn’t provide a lot of them.

From the help:

About calculated  columns

Calculated columns display the results of mathematical or logical operations. The operations can include information from one or more other columns in a list as well as system  functions such as [today] to indicate the current date. For example, if you want to add the value of column A with the value of column B, you can set up a calculated column to display the result. In the following example, the "Sum" column is the calculated column.

Column 1 Column 2 Sum
1 2 3
10 20 30
16 16 32
The formula  you would enter to create  this calculated column is:

[column 1] + [column 2]

A calculated column enables you to perform operations on data in any other column. And the columns need not be side-by-side or even next to the calculated column.

You can also use other operations for creating calculated columns. For example, you can combine text  found in two columns. For example, you can create a column that combines the text found in the columns First Name and Last Name, as in the following example.

Family Name First Name Full Name
Harrington Mark Harrington, Mark
Pak Jae Pak, Jae
Reinhart Marie Reinhart, Marie
You can combine the text in the first two columns by entering the following formula for the calculated column:

[Family Name] & ", " & [First Name]

The logical & (AND) operators in this formula combine the text in the first column with a comma and space, and then the text from the second column.

You can also create calculated columns that work  with calendar dates. In the following example, the calculated column automatically fills in the date when a business process will be completed. The formula assumes that the process takes seven days to complete.

Date Submitted Work Complete
12/05 12/12
12/06 12/13
12/07 12/14
The formula for this calculated column is:

[Date Submitted] + 7

Note When entering a specific date into a calculation, make sure you use the date function. For example, to enter the date November 2, 2004, you would enter: =DATE(2004,11,02)

About formulas

Formulas are equations that perform calculations on values in a list. A formula starts with an equal sign (=). For example, the following formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

You can use a formula in a calculated column and to calculate default values for a column.

A formula can also contain any or all of the following: functions, column references, operators, and constants.

Parts of a formula
=PI()*[Result]^2

Functions The PI() function  returns the value of pi: 3.142...

References (or column names) [Result] represents the value in the Result column for the current row.

Constants Numbers or text values entered directly into a formula, such as 2.

Operators The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies.

A formulas might use one or more of the above elements. Here are some examples of formulas (in order of complexity).

Simple formulas (such as =128+345)

The following formulas contain literal values and operators.

Example Description
=128+345 Adds 128 and 345
=5^2 Squares 5
Formulas that contain column references (such as =[Revenue] – [Cost])

The following formulas refer to other columns in the same list.

Example Description
=[Revenue] Use the value in the Revenue column
=[Revenue]*10/100 10% of the value in the Revenue column
=[Revenue] > [Cost] Returns Yes if the value in the Revenue column is greater than the value in the Cost column
Formulas that call functions (such as =AVG(1, 2, 3, 4, 5))

The following formulas call built-in functions.

Example Description
=MAX(Q1, Q2, Q3, Q4) Returns the largest value in a set of values
=IF(Cost>Revenue, "Not OK", "OK") Returns Not OK if cost is greater than revenue. Else, returns OK.
=DAY("15-Apr-2008") Returns a day part of a date. This formula returns the number 15.
Formulas with nested functions (such as =SUM(ROUND([Cost],2),[Profit])

The following formulas specify one or more functions as function arguments.

Example Description
=SUM(IF(A>B, A-B, 10), C) The IF function returns the difference between the values in columns A and B, or 10.
The SUM function adds the return value of the IF function and the value in column C.

=DEGREES(PI()) The PI function returns the number 3.14159265358979.
The DEGREES function converts a value specified in radians to degrees. This formula returns the value 180.

=ISNUMBER(FIND("BD",Column1)) The FIND function searches for the string BD in Column1 and returns the starting position of the string. It returns an error  value if the string is not found.
The ISNUMBER function returns Yes if the FIND function returned a numeric value. Else, it returns No.

About functions

Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. Functions can be used to perform simple or complex calculations. For example, the ROUND function rounds off a number in the Cost column.

=ROUND(Cost, 2)

Structure The structure of a function begins with an equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis.

Function name Name of a function that is supported  by lists. Each function takes a specific number of arguments, processes them, and returns a value.

Arguments Arguments can be numbers, text, logical values such as True or False, or column references. The argument you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.

Argument tooltip A tooltip with the syntax  and arguments appears  as you type the function. For example, type =ROUND( and the tooltip appears. Tooltips only appear for built-in functions.

Nested functions

In certain cases, you may need to use a function as one of the arguments of another function. For example, the following formula uses a nested AVERAGE function and compares the result with the sum of two column values.

=AVERAGE([Cost1], SUM([Cost2]+[Discount]))

Valid returns When a function is used as an argument, it must return the same type of value that the argument uses. For example, if the argument uses Yes or No, then the nested function must return Yes or No. If it doesn't, the list displays a #VALUE! error value.

Nesting level limits A formula can contain up to eight levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. For instance, the SUM function is a second-level function because it is an argument of the AVERAGE function. A function nested within the SUM function would be a third-level function, and so on.

Note Lists do not support the RAND and NOW functions. The TODAY and ME functions are not supported in calculated columns, but are supported in the default value setting of a column.

About column references in a formula

A reference identifies a cell in the current row in a datasheet and tells a list where to look for the values or data you want to use in a formula. For example, [Cost] references the value in the Cost column in the current row. If the Cost column has the value of 100 for the current row, then =[Cost]*3 will return 300.

With references, you can use data contained in different columns of a list in one or more formulas. Columns of the following data types can be referenced in a formula: Single line of text, number, currency, date and time, choice, yes/no, and calculated.

You use the display name of the column to reference it in a formula. If the name includes a space or a special character, you must enclose the name in square brackets ([ ]). References are not case-sensitive. For example, you could reference the Unit Price column in a formula as [Unit Price] or [unit price].

Note You cannot reference a value in a row other than the current row. You also cannot include column references in a formula that is specified as the default value of a column.

About calculation operators

Operators specify the type of calculation that you want to perform on the elements of a formula. Lists support three different types of calculation operators: arithmetic, comparison, and text.

Types of operators

Arithmetic operators To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Arithmetic operator Meaning (Example)
+ (plus sign) Addition (3+3)
– (minus sign) Subtraction (3–1)
Negation (–1)
* (asterisk) Multiplication (3*3)
/ (forward slash) Division (3/3)
% (percent sign) Percent (20%)
^ (caret) Exponentiation (3^2)
Comparison operators You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value of Yes or No.

Comparison operator Meaning (Example)
= (equal sign) Equal to (A=B)
> (greater than sign) Greater than (A>B)
< (less than sign) Less than (A<B)
>= (greater than or equal to sign) Greater than or equal to (A>=B)
<= (less than or equal to sign) Less than or equal to (A<=B)
<> (not equal to sign) Not equal to (A<>B)
Text concatenation operator Use the ampersand (&) to join, or concatenate, one or more text  strings to produce a single piece of text.

Text operator Meaning (Example)
& (ampersand) Connects, or concatenates, two values to produce one continuous text value ("North"&"wind")
The order in which a list performs operations in a formula

Formulas calculate values in a specific order. A list formula  might begin with an equal sign (=). Following the equal sign are the elements to be calculated  (the operands), which are separated by calculation operators. Lists calculate the formula from left to right, according to a specific order for each operator in the formula.

Operator precedence

If you combine several operators in a single formula, lists perform the operations in the order shown in the following table. If a formula contains operators with the same precedence — for example, if a formula contains both a multiplication and division operator — lists evaluate the operators from left to right.

Operator Description
– Negation (as in –1)
% Percent
^ Exponentiation
* and / Multiplication and division
+ and – Addition and subtraction
& Connects two strings of text (concatenation)
= < > <= >= <> Comparison
Use of parentheses

To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 11 because a list calculates multiplication before addition. The formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

In contrast, if you use parentheses to change the syntax, the list adds 5 and 2 together and then multiplies the result by 3 to produce 21.

=(5+2)*3

In the example below, the parentheses around the first part of the formula force the list to calculate [Cost]+25 first and then divide the result by the sum of the values in columns EC1 and EC2.

=([Cost]+25)/SUM([EC1]+[EC2])

About constants in formulas

A constant is a value that is not calculated. For example, the date 10/9/2008, the number 210, and the text "Quarterly Earnings" are all constants. Constants can be of the following data types:

String (Example: =[Last Name] = "Smith")
String constants are enclosed in double quotes and can be up to 255 characters.

Number (Example: =[Cost] >= 29.99)
Numeric constants can include decimal places, and can be positive or negative.

Date (Example: =[Date] <> 7/7/2002)
Date constants do not require delimiters.

Boolean (Example: =IF([Cost]>[Revenue], "Loss", "No Loss")
Yes and No are Boolean constants. You can use them in conditional expressions. In the above example, if Cost is greater than Revenue, the IF function  returns Yes, and the formula returns the string "Loss". If Cost is equal to or less than Revenue, the function returns No, and formula returns the string "No Loss".

Examples of common formulas

Note You can use the following examples in calculated columns. Examples that do not include column references can be used to specify the default value of a column.

Conditional formulas

Check if a number is greater than or less than another number

Use the IF function to do this task.

Column1 Column2 Formula Description
15000 9000 =Column1>Column2 Is Column1 greater than Column2? (Yes)
15000 9000 =IF(Column1<=Column2, "OK", "Not OK") Is Column1 less than or equal to Column2? (Not OK)
Return a logical value after comparing column contents

For a result that is a logical value (Yes or No), use the AND, OR, and NOT functions.
Column1 Column2 Column3 Formula Description
15 9 8 =AND(Column1>Column2, Column1<Column3) Is 15 greater than 9 and less than 8? (No)
15 9 8 =OR(Column1>Column2, Column1<Column3) Is 15 greater than 9 or less than 8? (Yes)
15 9 8 =NOT(Column1+Column2=24) Is 15 plus 9 not equal to 24? (No)
For a result that is another calculation, or any other value other than Yes or No, use the IF, AND, and OR functions.

Column1 Column2 Column3 Formula Description
15 9 8 =IF(Column1=15, "OK", "Not OK") If the value in Column1 equals 15, then return "OK". (OK)
15 9 8 =IF(AND(Column1>Column2, Column1<Column3), "OK", "Not OK") If 15 is greater than 9 and less than 8, then return "OK". (Not OK)
15 9 8 =IF(OR(Column1>Column2, Column1<Column3), "OK", "Not OK") If 15 is greater than 9 or less than 8, then return "OK". (OK)
Display zeroes as blanks or dashes

Use the IF function to do this task.
Column1 Column2 Formula Description
10 10 =Column1-Column2 Second number subtracted from the first (0)
10 10 =IF(Column1-Column2,"",Column1-Column2) Returns null when the value is zero (blank column)
15 9 =IF(Column1-Column2,"-",Column1-Column2) Returns a dash when the value is zero (-)
Date and time formulas

Add dates

To add a number of days to a date, use the addition (+) operator. Note that when manipulating dates, the return type of the calculated column must be set to Date and Time.
Column1 Column2 Formula Description
6/9/2007 3 =Column1+Column2 Add 3 days to 6/9/2007 (6/12/2007)
12/10/2008 54 =Column1+Column2 Add 54 days to 12/10/2008 (2/2/2009)
To add a number of months to a date, use the DATE, YEAR, MONTH, and DAY functions.

Column1 Column2 Formula Description
6/9/2007 3 =DATE(YEAR(Column1),MONTH(Column1)+Column2,DAY(Column1)) Add 3 months to 6/9/2007 (9/9/2007)
12/10/2008 25 =DATE(YEAR(Column1),MONTH(Column1)+Column2,DAY(Column1)) Add 25 months to 12/10/2008 (1/10/2011)
To add a number of years to a date, use the DATE, YEAR, MONTH, and DAY functions.

Column1 Column2 Formula Description
6/9/2007 3 =DATE(YEAR(Column1)+Column2,MONTH(Column1),DAY(Column1)) Add 3 years to 6/9/2007 (6/9/2010)
12/10/2008 25 =DATE(YEAR(Column1)+Column2,MONTH(Column1),DAY(Column1)) Add 25 years to 12/10/2008 (12/10/2033)
To add a combination of days, months, and years to a date, use the DATE, YEAR, MONTH, and DAY functions.

Column1 Formula Description
6/9/2007 =DATE(YEAR(Column1)+3,MONTH(Column1)+1,DAY(Column1)+5) Add 3 years, 1 month, and 5 days to 6/9/2007 (7/14/2010)
12/10/2008 =DATE(YEAR(Column1)+1,MONTH(Column1)+7,DAY(Column1)+5) Add 1 year, 7 months, and 5 days to 6/9/2007 (1/14/2009)
Calculate the difference between two dates

Use the DATEDIF function to do this task.
Column1 Column2 Formula Description
01-Jan-1995 15-Jun-1999 =DATEDIF(Column1, Column2,"d") Return the number of days between the two dates (1626)
01-Jan-1995 15-Jun-1999 =DATEDIF(Column1, Column2,"ym") Return the number of months between the dates, ignoring the year part (5)
01-Jan-1995 15-Jun-1999 =DATEDIF(Column1, Column2,"yd") Return the number of days between the dates, ignoring the year part (165)
Calculate the difference between two times

For presenting the result in the standard time format (hours:minutes:seconds), use the subtraction operator (-) and the TEXT function.
For this method to work, hours must not exceed 24, and minutes and seconds must not exceed 60.

Column1 Column2 Formula Description
06/09/2007 10:35 AM 06/09/2007 3:30 PM =TEXT(Column2-Column1,"h") Hours between two times (4)
06/09/2007 10:35 AM 06/09/2007 3:30 PM =TEXT(Column2-Column1,"h:mm") Hours and minutes between two times (4:55)
06/09/2007 10:35 AM 06/09/2007 3:30 PM =TEXT(Column2-Column1,"h:mm:ss") Hours,minutes, and seconds between two times (4:55:00)
For presenting the result in a total based on one time unit, use the INT function, or HOUR, MINUTE, and SECOND functions.

Column1 Column2 Formula Description
06/09/2007 10:35 AM 06/10/2007 3:30 PM =INT((Column2-Column1)*24) Total hours between two times (28)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =INT((Column2-Column1)*1440) Total minutes between two times (1735)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =INT((Column2-Column1)*86400) Total seconds between two times (104100)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =HOUR(Column2-Column1) Hours between two times, when the difference does not exceed 24. (4)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =MINUTE(Column2-Column1) Minutes between two times, when the difference does not exceed 60. (55)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =SECOND(Column2-Column1) Seconds between two times, when the difference does not exceed 60. (0)
Convert times

To convert hours from standard time format to a decimal number, use the INT function.
Column1 Formula Description
10:35 AM =(Column1-INT(Column1))*24 Number of hours since 12:00 AM (10.583333)
12:15 PM =(Column1-INT(Column1))*24 Number of hours since 12:00 AM (12.25)
To convert hours from decimal number to the standard time format (hours:minutes:seconds), use the divisor operator and the TEXT function.

Column1 Formula Description
10:5833 =TEXT(Column1/24, "h:mm") Hours since 12:00 AM (10:35)
12:25 =TEXT(Column1/24, "h:mm") Hours since 12:00 AM (12:15)
Insert Julian dates

The phrase "Julian date" is sometimes used to refer to a date format that is a combination of the current year, and the number of days since the beginning of the year. For example, January 1, 2007 is represented as 2007001 and December 31, 2007 is represented as 2003356.
There is also a Julian date commonly used in astronomy, which is a serial date system  starting on January 1, 4713 B.C.E.

Note This format is not based on the Julian calendar.

To convert a date to a Julian date, use the TEXT and DATEVALUE functions.

Column1 Formula Description
6/23/2007 =TEXT(Column1,"yy")&TEXT((Column1-DATEVALUE("1/1/"& TEXT(Column1,"yy"))+1),"000") Date in "Julian" format, with a two-digit year (07174)
6/23/2007 =TEXT(Column1,"yyyy")&TEXT((Column1-DATEVALUE("1/1/"&TEXT(Column1,"yy"))+1),"000") Date in "Julian" format, with a four-digit year (2007174)
To convert a date to a Julian date used in astronomy, use the constant 2415018.50.

This formula only works  for dates after 3/1/1901, and if you are using the 1900 date system.

Column1 Formula Description
6/23/2007 =Column1+2415018.50 Date in "Julian" format, used in astronomy (2454274.50)
Show dates as the day of the week

To convert dates to the text for the day of the week, use the TEXT and WEEKDAY functions.
Column1 Formula Description
19-Feb-2007 =TEXT(WEEKDAY(Column1), "dddd") Calculates the day of the week for the date and returns the full name of the day (Monday)
3-Jan-2008 =TEXT(WEEKDAY(Column1), "ddd") Calculates the day of the week for the date and returns the abbreviated name of the day (Thu)
Math formulas

Add numbers

To add numbers in two or more columns in a row, use the addition operator or the SUM function.

Column1 Column2 Column3 Formula Description
6 5 4 =Column1+Column2+Column3 Add the values in the first three columns (15)
6 5 4 =SUM(Column1,Column2,Column3) Add the values in the first three columns (15)
6 5 4 =SUM(IF(Column1>Column2, Column1-Column2, 10), Column3) If Column1 is greater than Column2, add the difference and Column3. Else add 10 and Column3. (5)
Subtract numbers

Use the subtraction (-) operator to do this task.
Column1 Column2 Column3 Formula Description
15000 9000 -8000 =Column1-Column2 Subtract 9000 from 15000 (6000)
15000 9000 -8000 =SUM(Column1, Column2, Column3) Add numbers in the first three columns, including negative values (16000)
Calculate the difference between two numbers as a percentage

Use the subtraction (-) and division (/) operators, and the ABS function.
Column1 Column2 Formula Description
2342 2500 =(Column2-Column1)/ABS(Column1) Percentage change (6.75% or 0.06746)
Multiply numbers

Use the multipliation (*) operator or the PRODUCT function  to do this task.
Column1 Column2 Formula Description
5 2 =Column1*Column2 Multiplies the numbers in the first two columns (10)
5 2 =PRODUCT(Column1, Column2) Multiplies the numbers in the first two columns (10)
5 2 =PRODUCT(Column1,Column2,2) Multiplies the numbers in the first two columns and the number 2 (20)
Divide numbers

Use the division operator (/) to do this task.

Column1 Column2 Formula Description
15000 12 =Column1/Column2 Divides 15000 by 12 (1250)
15000 12 =(Column1+10000)/Column2 Adds 15000 and 9000, and then divides the total by 12 (2000)
Calculate the average of numbers

The average is also called the mean. To calculate the average of numbers in two or more columns in a row, use the AVERAGE function.

Column1 Column2 Column3 Formula Description
6 5 4 =AVERAGE(Column1, Column2,Column3) Average of the numbers in the first three columns (5)
6 5 4 =AVERAGE(IF(Column1>Column2, Column1-Column2, 10), Column3) If Column1 is greater than Column, calculate the average of the difference and Column3. Else calculate the average of the value 10 and Column3. (2.5)
Calculate the median of numbers

The median is the value at the center of an ordered range of numbers. Use the MEDIAN function to calculate the median of a group of numbers.
A B C D E F Formula Description
10 7 9 27 0 4 =MEDIAN(A, B, C, D, E, F) Median of numbers in the first 6 columns (8)
Calculate the smallest or largest number in a range

To calculate the smallest or largest number in two or more columns in a row, use the MIN and MAX functions.
Column1 Column2 Column3 Formula Description
10 7 9 =MIN(Column1, Column2, Column3) Smallest number (7)
10 7 9 =MAX(Column1, Column2, Column3) Largest number (10)
Count values

To count numeric values, use the COUNT function.
Column1 Column2 Column3 Formula Description
Apple 12/12/2007 =COUNT(Column1, Column2, Column3) Counts the number of columns that contain numeric values, including date and time values. Exludes text  and null values. (1)
$12 #DIV/0! 1.01 =COUNT(Column1, Column2, Column3) Counts the number of columns that contain numeric values, but excludes error  and logical values (2)
Increase or decrease a number by a percentage

Use the percentage (%) operator to do this task.
Column1 Column2 Formula Description
23 3% =Column1*(1+5%) Increases number in Column1 by 5% (24.15)
23 3% =Column1*(1+Column2) Increase number in Column1 by the percent value in Column2: 3% (23.69)
23 3% =Column1*(1-Column2) Decrease number in Column1 by the percent value in Column2: 3% (22.31)
Raise a number to a power

Use the exponent (^) operator or the POWER function to do this task.
Column1 Column2 Formula Description
5 2 =Column1^Column2 Calculates five squared (25)
5 3 =POWER(Column1, Column2) Calculates five cubed (125)
Round a number

To round up a number, use the ROUNDUP, ODD, and EVEN functions.

Column1 Formula Description
20.3 =ROUNDUP(Column1,0) Rounds 20.3 up to the nearest whole number (21)
-5.9 =ROUNDUP(Column1,0) Rounds -5.9 up (-6)
12.5493 =ROUNDUP(Column1,2) Rounds 12.5493 up to the nearest hundredth, two decimal places (12.55)
20.3 =EVEN(Column1) Rounds 20.3 up to the nearest even number (22)
20.3 =ODD(Column1) Rounds 20.3 up to the nearest odd number (21)
To round down a number, use the ROUNDDOWN function.

Column1 Formula Description
20.3 =ROUNDDOWN(Column1,0) Rounds 20.3 down to the nearest whole number (20)
-5.9 =ROUNDDOWN(Column1,0) Rounds -5.9 down (-5)
12.5493 =ROUNDDOWN(Column1,2) Rounds 12.5493 down to the nearest hundredth, two decimal places (12.54)
To round a number to the nearest number or fraction, use the ROUND function.

Column1 Formula Description
20.3 =ROUND(Column1,0) Rounds 20.3 down, because the fraction part is less than .5 (20)
5.9 =ROUND(Column1,0) Rounds 5.9 up, because the fraction part is greater than .5 (6)
-5.9 =ROUND(Column1,0) Rounds -5.9 down, because the fraction part is less than -.5 (-6)
1.25 =ROUND(Column1, 1) Rounds the number to the nearest tenth (one decimal place). Because the portion to be rounded is 0.05 or greater, the number is rounded up (result: 1.3)
30.452 =ROUND(Column1, 2) Rounds the number to the nearest hundredth (two decimal places). Because the portion to be rounded, 0.002, is less than 0.005, the number is rounded down (result: 30.45)
To round a number to the significant digit above 0, use the ROUND, ROUNDUP, ROUNDDOWN, INT, and LEN functions.

Column1 Formula Description
5492820 =ROUND(Column1,3-LEN(INT(Column1))) Rounds the number to 3 significant digits (5490000)
22230 =ROUNDDOWN(Column1,3-LEN(INT(Column1))) Rounds the bottom number down to 3 significant digits (22200)
5492820 =ROUNDUP(Column1, 5-LEN(INT(Column1))) Rounds the top number up to 5 significant digits (5492900)
Text formulas

Change the case of text

Use the UPPER, LOWER, or PROPER functions to do this task.
Column1 Formula Description
nancy Davolio =UPPER(Column1) Changes text to uppercase (NANCY DAVOLIO)
nancy Davolio =LOWER(Column1) Changes text to lowercase (nancy davolio)
nancy Davolio =PROPER(Column1) Changes text to title case (Nancy Davolio)
Combine first and last names

Use the ampersand (&) operator or the CONCATENATE function to do this task.
Column1 Column2 Formula Description
Nancy Fuller =Column1&Column2 Combines the two strings (NancyFuller)
Nancy Fuller =Column1&" "&Column2 Combines the two strings, separated by a space (Nancy Fuller)
Nancy Fuller =Column2&","&Column1 Combines the two strings, separated by a comma (Fuller, Nancy)
Nancy Fuller =CONCATENATE(Column2, ",", Column1) Combines the two strings, separated by a comma (Fuller,Nancy)
Combine text and numbers from different columns

Use the CONCATENATE and TEXT functions, and the ampersand (&) operator to do this task.
Column1 Column2 Formula Description
Buchanan 28 =Column1&" sold "&Column2&" units." Combines contents above into a phrase (Buchanan sold 28 units)
Dodsworth 40% =Column1&" sold "&TEXT(Column2,"0%")&" of the total sales." Combines contents above into a phrase (Dodsworth sold 40% of the total sales).
Note The TEXT function appends the formatted value of Column2 instead of the underlying value, which is .4.

Buchanan 28 =CONCATENATE(Column1," sold ",Column2," units.") Combines contents above into a phrase (Buchanan sold 28 units)
Combine text with a date or time

Use the TEXT function and the ampersand (&) operator to do this task.
Column1 Column2 Formula Description
Billing Date 5-Jun-2007 ="Statement date: "&TEXT(Column2, "d-mmm-yyyy") Combine text with a date (Statement date: 5-Jun-2007)
Billing Date 5-Jun-2007 =Column1&" "&TEXT(Column2, "mmm-dd-yyyy") Combine text and date from difference columns into one column (Billing Date Jun-05-2007)
Compare column contents

To compare one column to another column or a list of values, use the EXACT and OR functions.
Column1 Column2 Formula Description
BD122 BD123 =EXACT(Column1,Column2) Compare contents of first two columns (No)
BD122 BD123 =EXACT(Column1, "BD122") Compare contents of Column1 and the string "BD122" (Yes)
BD122 BD123 =OR(EXACT("BD121", Column1, Column2) Compare the string "BD121" with the contents of the first two columns (No)
Check if a column value or a part of it matches specific text

To check if a column value or a part of it matches specific text, use the IF, FIND, SEARCH, and ISNUMBer functions.

Column1 Formula Description
Davolio =IF(Column1="Davolio", "OK", "Not OK") Checks to see if Column1 is Davolio (OK)
Davolio =IF(ISNUMBER(FIND("v",Column1)), "OK", "Not OK") Checks to see if Column1 contains the letter v (OK)
BD123 =ISNUMBER(FIND("BD",Column1)) Checks to see if Column1 contains BD (Yes)
Count nonblank columns

Use the COUNTA function to do this task.
Column1 Column2 Column3 Formula Description
Sales 19 =COUNTA(Column1, Column2) Counts the number of nonblank columns (2)
Sales 19 =COUNTA(Column1, Column2, Column3) Counts the number of nonblank columns (2)
Remove characters from text

Use the LEN, LEFT, and RIGHT functions to do this task.
Column1 Formula Description
Vitamin A =LEFT(Column1,LEN(Column1)-2) Return 7 (9-2) characters, starting from left (Vitamin)
Vitamin B1 =RIGHT(Column1, LEN(Column1)-8) Return 2 (10-8) characters, starting from right (B1)
Remove spaces from the beginning and end of a column

Use the TRIM function to do this task.
Column1 Formula Description
Hello there! =TRIM(Column1) Remove the spaces from the beginning and end (Hello there!)
Repeat a characater in a column

Use the REPT function to do this task.
Formula Description
=REPT(".",3) Repeats a period 3 times (...)
=REPT("-",10) Repeats a dash 10 times (----------)
Other formulas

Hide error values in columns

To display a dash, #N/A, or NA in place of an error value, use the ISERROR function.
Column1 Column2 Formula Description
10 0 =Column1/Column2 Results in an error (#DIV/0)
10 0 =IF(ISERROR(Column1/Column2),"NA",Column1/Column2) Returns NA when the value is an error
10 0
=IF(ISERROR(Column1/Column2),"-",Column1/Column2)

Quirky tip
If you need to use [Today] or [Me] in a situation where those values are not allowed in the calculation, simply create  a column on the list called "today" or "me" respectively, use it in the formula  and then delete the column. If you ever need to edit the formula you will need to temporarilly recreate the columns so the formula will pass its edits.

Thanks for this posting I found it useful. I was trying to write too
many nested IF funtions (more than the allowed 8). The best solution
I found to my problem was to "OR" my way out so I used less nested
IFs.