# Text Formulas for the Calculated Column

September 6, 2012 9 Comments

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

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

Pingback: Conditional Formulas for the Calculated Column « SharePoint Diva

Pingback: Mathematical Information for the Calculated Column « SharePoint Diva

Thanks for sharing your thoughts. I really appreciate your efforts and

I will be waiting for your next post thank you once again.

Reblogged this on crazyforsharepoint and commented:

Some really useful stuff for writing calculated columns formalae

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

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

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)))

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)