TEXT
The TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. For example, suppose field A1 contains the number 23.5. To format the number as a dollar amount, you can use the following formula:
TEXT(A1,”$0.00″)
In this example, it will display as $23.50.
You can also add text to the preceding formula
TEXT(A1,”$0.00″) & ” per hour”
In this example, it will display as $23.50 per hour.
The syntax is:
TEXT(value, format_text)
The “format_text” section can be customized however you would like. For example “m/d/yyyy” or “#,##0.00”.
PlaceholderDisplay0 (zero)Displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 8.9, and you want it to be displayed as 8.90, use the format #.00#Follows the same rules as the 0 (zero). However, it does not display extra zeros when the number you type has fewer digits on either side of the decimal than there are #symbols in the format. For example, if the custom format is #.##, and you type 8.9 in the cell, the number 8.9 is displayed.?Follows the same rules as the 0 (zero). However, it adds a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, the custom format 0.0? aligns the decimal points for the numbers 8.9 and 88.99 in a column.. (period)Displays the decimal point in a number.
Notes
If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. If the format contains only number signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point; for example, .47.
CONCATENATE
The CONCATENATE function combines up to 255 text strings into one text string. The combined items can be text, numbers, field references, or a combination of all items.
The syntax is:
CONCATENATE(term1,term2,term3)
For example,
CONCATENATE(“KiSS”,” “,”Flow”)
would result in
KiSS Flow [the “ “ denotes a space character]
SUBSTITUTE
The SUBSTITUTE function can be used to replace a substring with another.
The syntax is:
SUBSTITUTE(FieldID,char(10),”<br>”)
When a user inputs data, every <Enter Key> pressed will be substituted as a new line. The data entered after the <Enter Key> will appear in a new line.
For example, let’s say you have a field called Meeting Summary, but you want to format the field to display better in emails that are sent. Here’s what is entered in the field.
Sam will be running the daily reports. <Enter Key> He will also follow up with the necessary departments on the pending actions. <Enter Key> He will follow up with all the staffs on their day-to-day tasks and update the manager at the end of the week.
In a new field that you might call Email Meeting Summary, enter this formula:
SUBSTITUTE(MeetingSummary,char(10),“<br>”)
In the Define Workflow section, select the Send an Email action, and then include the Email Meeting Summary as one of the fields to display in the email.
The result in the email will be:
Sam will be running the daily reports.
He will also follow up with the necessary departments on the pending actions.
He will follow up with all the staffs on their day-to-day tasks and update the manager at the end of the week.
To replace special characters with a space:
You can replace special characters with spaces using the SUBSTITUTE function. The formula looks like this
SUBSTITUTE(Field ID,("Special Character")," ")For example, let’s say the person filling a field called Phone Number gives a phone number of 804-223-1863, but you want it to display with a space instead of the hyphens.
In a new field, enter this formula
SUBSTITUTE(PhoneNumber,("-")," ")The result will be 804 223 1863
If you want to replace more than one special character, you can enter a formula like this:
SUBSTITUTE(SUBSTITUTE(Number,("-")," "),("&")," ")
MID
MID returns a specific number of characters from a text, starting at the position you specify, based on the number of characters you specify. MID always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
The syntax is:
MID(text, start_num, num_chars)
“Text” is the text string containing the characters you want to extract.
“start_num” is the position of the first character you want to extract in the text. The first character in text has start_num 1, and so on.
“num_chars” is the number of characters you want to display.
Notes:
If start_num is greater than the length of text, MID returns “” (empty text).
If start_num is less than the length of text, but start_num plus num_chars exceeds the length of text, MID returns the characters up to the end of text.
If start_num is less than 1, MID returns the #VALUE! error value.
If num_chars is negative, MID returns the #VALUE! error value.
LEN
LEN returns the number of characters in a text string.
The syntax is:
LEN(text)
E.g: LEN(“kissflow”) returns 8
LEFT
LEFT returns the first character or characters in a text string, based on the number of characters you specify.
The syntax is:
LEFT(text, [num_chars])
Notes
If num_chars is greater than the length of text, LEFT returns all of text.
If num_chars is omitted, it is assumed to be 1.