Text Formulas for the Calculated Column

This is a continuation of an attempt to group the information needed for Calculated Columns in a meaningful way. Some items appear on more than one list since this exercise can be subjective.

Examples of common formulas: Text formulas

Other Text Functions:

AND function Returns the logical value TRUE if all of the arguments are TRUE; returns FALSE if one or more arguments is FALSE.
ASC function For Double-byte character set (DBCS) languages, changes full-width (double-byte) characters to half-width (single-byte) characters.
AVERAGEA function Calculates the average (arithmetic mean) of the values in the list of arguments.
CHAR function Returns the character specified by a number.
CHOOSE function Uses index_num to return a value from the list of value arguments.
CLEAN function Removes all nonprintable characters from text.
CODE function Returns a numeric code for the first character in a text string.
CONCATENATE function Use the CONCATENATE function to join several text strings into one string.
COUNTA function Counts the number of arguments that are not empty.
DOLLAR function Converts a number to text using currency format, with the decimals rounded to the specified place.
EXACT function Compares two text strings and returns the logical value TRUE if they are exactly the same, FALSE otherwise.
FIND function Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text.
FIXED function Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
IF function Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
LEFT function LEFT returns the first character or characters in a text string, based on the number of characters you specify.
LEN function LEN returns the number of characters in a text string.
LOWER function Converts all uppercase letters in a text string to lowercase.
MAXA function Returns the largest value in a list of arguments.
Me function Returns the current user name.
MID function MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
MINA function Returns the smallest value in the list of arguments.
OR function Returns Yes if any argument is TRUE; returns No if all arguments are FALSE.
PROPER function Capitalizes the first letter and any other letters that follow a non-letter character in a text string.
REPLACE function REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.
REPT function Repeats text a given number of times.
RIGHT function RIGHT returns the last characters in a text string, based on the number of characters you specify.
ROMAN function Converts an arabic numeral to roman, as text.
SEARCH function SEARCH returns the number of the character at which a specific character or text string is first found, beginning with start_num.
T function Returns the text referred to by value.
TEXT function Converts a value to text in a specific number format.
TRIM function Removes all spaces from text except for single spaces between words.
TRUE function Returns the logical value TRUE.
UPPER function Converts text to uppercase.
USDOLLAR function Converts a number to text using currency format, with the decimals rounded to the specified place.
VALUE function Converts a text string that represents a number to a number.

Date and Time Information for the Calculated Column

Text Formulas for the Calculated Column

Conditional Formulas for the Calculated Column

Mathematical Information for the Calculated Column

Advertisements

9 Responses to Text Formulas for the Calculated Column

  1. Pingback: Date and Time Information for the Calculated Column « SharePoint Diva

  2. Pingback: Conditional Formulas for the Calculated Column « SharePoint Diva

  3. Pingback: Mathematical Information for the Calculated Column « SharePoint Diva

  4. Mickey says:

    Thanks for sharing your thoughts. I really appreciate your efforts and
    I will be waiting for your next post thank you once again.

  5. mansidesai26 says:

    Reblogged this on crazyforsharepoint and commented:
    Some really useful stuff for writing calculated columns formalae

  6. Staci Fitzpatrick says:

    I’ve been looking for this info for 15 minutes. I don’t know why I didn’t start here.

  7. Janice says:

    Hi admin, i found this post on 15 spot in google’s search results.
    I’m sure that your low rankings are caused by high bounce rate.
    This is very important ranking factor. One
    of the biggest reason for high bounce rate is due to visitors hitting the back
    button. The higher your bounce rate the further down the search
    results your posts and pages will end up, so having reasonably low bounce
    rate is important for improving your rankings naturally.
    There is very useful wordpress plugin which can help you.
    Just search in google for:
    Seyiny’s Bounce Plugin

  8. Jay C says:

    Good find.. I’m trying to come up with a formula that will convert K, M, or B into thousands, millions, or billions, with a value. I can’t seem to make this work:
    Column [Enter Value] is text, and this column [Item Value] is numeric. User should enter data as 14m for 14 million, and so on. The expected result is to end up with a numeric value.

    =IF(PROPER(RIGHT([Enter Value],1))=”M”,VALUE(LEFT([Enter Value]),(LEN([Enter Value])-1))*1000000
    ,IF(PROPER(RIGHT([Enter Value],1))=”B”,VALUE(LEFT([Enter Value]),(LEN([Enter Value])-1))*1000000000
    ,IF(PROPER(RIGHT([Enter Value],1))=”K”,VALUE(LEFT([Enter Value]),(LEN([Enter Value])-1))*1000,0)))

    • Jay C says:

      Nevermind, I figured it out. Just took two hours of staring at it profusely.

      =IF(PROPER(RIGHT([Enter Value],1))=”K”,VALUE(LEFT([Enter Value],(LEN([Enter Value])-1))*1000)
      ,IF(PROPER(RIGHT([Enter Value],1))=”M”,VALUE(LEFT([Enter Value],(LEN([Enter Value])-1))*1000000)
      ,IF(PROPER(RIGHT([Enter Value],1))=”B”,VALUE(LEFT([Enter Value],(LEN([Enter Value])-1))*1000000000),0)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: