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]

 

 

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.
Did this answer your question?