Text Functions

The text function enables you to return part of a string, search for text within a string, or concatenate string values. Some additional functions help you to control the formats for dates, times, and numbers.

Following is the list of text functions supported in Wyn Enterprise:

• CONCATENATE
• CONCATENATEX
• FIND
• LEFT
• LEFTTRIM
• LEN
• LOWER
• MID
• REPLACE
• RIGHT
• RIGHTTRIM
• UPPER

CONCATENATE

---

Description

The CONCATENATE function joins two text strings into one text string.

Syntax

CONCATENATE(<text1>, <text2>)

Parameters

Parameter	Description
text1, text2	The text strings to be joined into a single text string. Strings can include text or numbers. You can also use column references.

Return Value

The concatenated string.

Example

CONCATENATE("Year:",Year('Sale'[Date]))

CONCATENATEX

---

Description

The CONCATENATEX function concatenates the result of an expression evaluated for each row in a table.

Syntax

CONCATENATEX(<table>, <expression> [,delimiter])

Parameters

Parameter	Description
table	The table containing the rows for which the expression will be evaluated.
expression	The expression to be evaluated for each row of the table.
delimiter (optional)	A separator.

Return Value

A text string.

Example

CONCATENATEX(Addresses, [Country] & " " & [City], ",")

FIND

---

Description

The FIND function returns the starting position of one text string within another text string. FIND is case-sensitive.

Syntax

FIND(<find_text>, <within_text>)

Parameters

Parameter	Description
find_text	The text you want to find. Use double quotes (empty text) to match the first character in within_text.
within_text	The text containing the text you want to find.

Return Value

The number shows the starting point of the text string you want to find.

Example

FIND("Jack", 'Customer'[Name])

LEFT

---

Description

The LEFT function returns the specified number of characters from the start of a text string.

Syntax

LEFT(<text>, <num_chars>)

Parameters

Parameter	Description
text	The text string containing the characters you want to extract, or a reference to a column that contains the text.
num_chars	The number of characters you want LEFT to extract.

Return Value

A text string.

Example

LEFT('Product'[Name], 3)

LEFTTRIM

---

Description

The LEFTTRIM function removes all spaces from text left except for single spaces between words.

Syntax

LEFTTRIM(<text>)

Parameters

Parameter	Description
text	The text from which you want spaces removed, or a column that contains the text.

Return Value

The string with spaces removed.

Example

LEFTTRIM('Product'[ColorName])

LEN

---

Description

The LEN function returns the number of characters in a text string.

Syntax

LEN(<text>)

Parameters

Parameter	Description
text	The text whose length you want to find, or a column that contains the text. Spaces count as characters.

Return Value

A whole number that indicates the number of characters in the text string.

Example

LEN('Product'[Name])

LOWER

---

Description

The LOWER function converts all letters in a text string to lowercase.

Syntax

LOWER(<text>)

Parameters

Parameter	Description
text	The text you want to convert to lowercase or a reference to a column that contains the text.

Return Value

Text in lowercase.

Example

LOWER('Product'[Name])

MID

---

Description

The MID function returns a string of characters from the middle of a text string, given a starting position and length.

Syntax

MID(<text>, <start_num>, <num_chars>)

Parameters

Parameter	Description
text	The text string from which you want to extract the characters or a column that contains the text.
start_num	The position of the first character you want to extract. Positions start at 1.
num_chars	The number of characters to return.

Return Value

A string of text of the specified length.

Example

MID("abcde", 2, 1))

REPLACE

---

Description

The REPLACE function replaces part of a text string, with a different text string.

Syntax

REPLACE(<source>, <old_text>, <new_text>)

Parameters

Parameter	Description
source	The text string from which you want to extract the characters, or a column that contains the text.
old_text	The string of text that contains the characters you want to replace, or a reference to a column that contains the text.
new_text	The replacement text for the specified characters in old_text.

Return Value

A text string.

Example

REPLACE('Products'[ProductName],"OB","OC")

RIGHT

---

Description

The RIGHT function returns the last character or characters in a text string, based on the number of characters you specify.

Syntax

RIGHT(<text>, <num_chars>)

Parameters

Parameter	Description
text	The text string that contains the characters you want to extract, or a reference to a column that contains the text.
num_chars	The number of characters you want RIGHT to extract. You can also use a reference to a column that contains numbers.

Return Value

A text string containing the specified right-most characters.

Example

RIGHT('Products'[ProductName], 3)

RIGHTTRIM

---

Description

The RIGHTTRIM function removes all spaces from text right except for single spaces between words.

Syntax

RIGHTTRIM(<text>)

Parameters

Parameter	Description
text	The text from which you want spaces removed, or a column that contains the text.

Return Value

The string with spaces removed.

Example

RIGHTTRIM('Product'[ColorName])

UPPER

---

Description

The UPPER function converts a text string to all uppercase letters.

Syntax

UPPER (<text>)

Parameters

Parameter	Description
text	The text you want to be converted to uppercase, or a reference to a column that contains the text.

Return Value

The same text, in uppercase.

Example

UPPER ('Product'[Name])