Power BI: DAX: Text Functions
Data Analysis Expressions (DAX) includes a set of text functions that is based on the library of string functions in Excel, but it has been modified to work with tables and columns.
Read more about the following text functions:
Original article (before wiki edits) was written by Jeannine Takaki and JuanPablo Jofre and formatted by Mary Browning, Microsoft SQL Server Technical Writers.
BLANK
Returns a blank.
Blanks are not equivalent to nulls. DAX uses blanks for both database nulls and for blank cells in Excel. For more information, see Data Types Supported in PowerPivot Workbooks in the TechNet Library.
Some DAX functions treat blank cells somewhat differently from Microsoft Excel. Blanks and empty strings ("") are not always equivalent, but some operations may treat them as such. For details on the behavior of an individual function or operator, see the list of types of functions on the Data Analysis Expressions (DAX) page.
Syntax
BLANK()
Example
The following example illustrates how you can work with blanks in formulas. The formula calculates the ratio of sales between the Resellers and the Internet channels. However, before attempting to calculate the ratio the denominator should be checked for zero values. If the denominator is zero then a blank value should be returned; otherwise, the ratio is calculated.
=IF( SUM(InternetSales_USD[SalesAmount_USD])= 0 , BLANK() , SUM(ResellerSales_USD[SalesAmount_USD])/SUM(InternetSales_USD[SalesAmount_USD]) )
The table shows the expected results when this formula is used to create a PivotTable.
| Reseller to Internet Sales Ratio | Column Labels | |||
| Row Labels | Accessories | Bikes | Clothing | Grand Total |
| 2001 | 2.65 | 2.89 | ||
| 2002 | 3.33 | 4.03 | ||
| 2003 | 1.04 | 2.92 | 6.63 | 3.51 |
| 2004 | 0.41 | 1.53 | 2.00 | 1.71 |
| Grand Total | 0.83 | 2.51 | 5.45 | 2.94 |
Note that, in the original data source, the column evaluated by the BLANK function might have included text, empty strings, or nulls. If the original data source was a SQL Server database, nulls and empty strings are different kinds of data. However, for this operation an implicit type cast is performed and DAX treats them as the same.
CONCATENATE
Joins two text strings into one text string.
The CONCATENATE function joins two text strings into one text string. The joined items can be text, numbers or Boolean values represented as text, or a combination of those items. You can also use a column reference if the column contains appropriate values.
The CONCATENATE function in DAX accepts only two arguments, whereas the Excel CONCATENATE function accepts up to 255 arguments. If you need to concatenate multiple columns, you can create a series of calculations or, better, use the concatenation operator (&) to join all of them in a simpler expression.
If you want to use text strings directly, rather than using a column reference, you must enclose each string in double quotation marks.
Syntax
CONCATENATE(<text1>, <text2>)
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.
Returns the concatenated string.
Example: Concatenation of Literals
The sample formula creates a new string value by combining two string values that you provide as arguments.
=CONCATENATE("Hello ", "World")
Example: Concatenation of Strings in Columns
The sample formula returns the customer's full name as listed in a phone book. Note how a nested function is used as the second argument. This is one way to concatenate multiple strings, when you have more than two values that you want to use as arguments.
=CONCATENATE(Customer[LastName], CONCATENATE(", ", Customer[FirstName]))
Example: Conditional Concatenation of Strings in Columns
The sample formula creates a new calculated column in the Customer table with the full customer name as a combination of first name, middle initial, and last name. If there is no middle name, the last name comes directly after the first name. If there is a middle name, only the first letter of the middle name is used and the initial letter is followed by a period.
=CONCATENATE( [FirstName]&" ", CONCATENATE( IF( LEN([MiddleName])>1, LEFT([MiddleName],1)&" ", ""), [LastName]))
This formula uses nested CONCATENATE and IF functions, together with the ampersand (&) operator, to conditionally concatenate three string values and add spaces as separators.
Example: Concatenation of Columns with Different Data Types
The following example demonstrates how to concatenate values in columns that have different data types. If the value that you are concatenating is numeric, the value will be implicitly converted to text. If both values are numeric, both values will be cast to text and concatenated as if they were strings.
| Product description | Product abbreviation (column 1 of composite key) |
Product number (column 2 of composite key) |
New generated key column |
| Mountain bike | MTN | 40 | MTN40 |
| Mountain bike | MTN | 42 | MTN42 |
=CONCATENATE('Products'[Product abbreviation],'Products'[Product number])
The CONCATENATE function in DAX accepts only two arguments, whereas the Excel CONCATENATE function accepts up to 255 arguments. If you need to add more arguments, you can use the ampersand (&) operator. For example, the following formula produces the results, MTN-40 and MTN-42.
=[Product abbreviation] & "-" & [Product number]
EXACT
Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. You can use EXACT to test text being entered into a document.
Syntax
EXACT(<text1>,<text2>)
text1. The first text string or column that contains text.
text2. The second text string or column that contains text.
Returns true or false (Boolean).
Example
The following formula checks the value of Column1 for the current row against the value of Column2 for the current row, and returns TRUE if they are the same, and returns FALSE if they are different.
=EXACT([Column1],[Column2])
FIND
Returns the starting position of one text string within another text string. FIND is case-sensitive.
Whereas Microsoft Excel has multiple versions of the FIND function to accommodate single-byte character set (SBCS) and double-byte character set (DBCS) languages, PowerPivot for Excel uses Unicode and counts each character the same way; therefore, you do not need to use a different version depending on the character type.
Syntax
FIND(<find_text, within_text, start_num)
find_text. The text you want to find. Use double quotes (empty text) to match the first character in within_text; wildcard characters not allowed.
within_text. The text containing the text you want to find.
start_num. The character at which to start the search; if omitted, start_num = 1. The first character in within_text is character number 1.
Returns a number that shows the starting point of the text string you want to find.
Example
The following formula finds the position of the first letter of the product designation, BMX, in the string that contains the product description.
=FIND("BMX","line of BMX racing goods")
FIXED
Rounds a number to the specified number of decimals and returns the result as text. You can specify that the result be returned with or without commas.
If the value used for the decimals parameter is negative, number is rounded to the left of the decimal point.
If you omit decimals, it is assumed to be 2.
If no_commas is 0 or is omitted, then the returned text includes commas as usual.
The major difference between formatting a cell containing a number by using a command and formatting a number directly with the FIXED function is that FIXED converts its result to text. A number formatted with a command from the formatting menu is still a number.
Syntax
FIXED(<number>, <decimals>, <no_commas>)
number. The number you want to round and convert to text, or a column containing a number.
decimals. (optional) The number of digits to the right of the decimal point; if omitted, 2.
no_commas. (optional) A logical value: if 1, do not display commas in the returned text; if 0 or omitted, display commas in the returned text.
Returns a number represented as text.
Example
The following example gets the numeric value for the current row in column, PctCost, and returns it as text with 4 decimal places and no commas.
=FIXED([PctCost],3,1)
Numbers can never have more than 15 significant digits, but decimals can be as large as 127.
FORMAT
Converts a value to text according to the specified format.
For information on how to use the format_string parameter, see the appropriate sections listed below (click the links to jump to those topics):
| To format | Follow these instructions |
| Numbers | Use predefined numeric formats or create user-defined numeric formats. |
| Dates and times | Use predefined date/time formats or create user-defined date/time formats. |
All predefined formatting strings use the current user locale when formatting the result.
Caution: Formatting strings are case sensitive. Different formatting can be obtained by using a different case. For example, when formatting a date value with the string "D" you get the date in the long format (according to your current locale). However, if you change the casing to "d" you get the date in the short format. Also, you might get unexpected results or an error if the intended formatting does not match the case of any defined format string.
Syntax
FORMAT(<value>, <format_string>)
value. A value or expression that evaluates to a single value.
format_string. A string with the formatting template.
Returns a string containing value formatted as defined by format_string.
Predefined Numeric Formats
The following table identifies the predefined numeric format names. These may be used by name as the style argument for the FORMAT function:
| Format specification | Description |
"General Number", "G", or "g" |
Displays number with no thousand separators. |
"Currency", "C", or "c" |
Displays number with thousand separators, if appropriate; displays two digits to the right of the decimal separator. Output is based on system locale settings. |
"Fixed", "F", or "f" |
Displays at least one digit to the left and two digits to the right of the decimal separator. |
"Standard", "N", or "n" |
Displays number with thousand separators, at least one digit to the left and two digits to the right of the decimal separator. |
"Percent" |
Displays number multiplied by 100 with a percent sign (%) appended immediately to the right; always displays two digits to the right of the decimal separator. |
"P", or "p" |
Displays number with thousandths separator multiplied by 100 with a percent sign (%) appended to the right and separated by a single space; always displays two digits to the right of the decimal separator. |
"Scientific" |
Uses standard scientific notation, providing two significant digits. |
"E", or "e" |
Uses standard scientific notation, providing six significant digits. |
"D", or "d" |
Displays number as a string that contains the value of the number in Decimal (base 10) format. This option is supported for integral types (Byte, Short, Integer, Long) only. |
"X", or "x" |
Displays number as a string that contains the value of the number in Hexadecimal (base 16) format. This option is supported for integral types (Byte, Short, Integer, Long) only. |
"Yes/No" |
Displays No if number is 0; otherwise, displays Yes. |
"True/False" |
Displays False if number is 0; otherwise, displays True. |
"On/Off" |
Displays Off if number is 0; otherwise, displays On. |
Remarks
Formatting strings are case sensitive. Different formatting can be obtained by using a different case. For example, when formatting a date value with the string "D" you get the date in the long format (according to your current locale); but, if you change the casing to "d" you get the date in the short format. Also, unexpected results or an error might occur if the intended formatting does not match the case of any defined format string.
Example
The following samples show the usage of different predefined formatting strings to format a numeric value.
FORMAT( 12345.67, "G")
FORMAT( 12345.67, "C")
FORMAT( 12345.67, "F")
FORMAT( 12345.67, "N")
FORMAT( 12345.67, "P")
FORMAT( 12345.67, "Scientific")
The above expressions return the following results:
12345.67 "G" displays the number with no formatting.
$12,345.67 "C" displays the number with your currency locale formatting. The sample here shows the default United States currency formatting.
12345.67 "F" displays at least one digit to the left of the decimal separator and two digits to the right of the decimal separator.
12,345.67 "N" displays at least one digit to the left of the decimal separator and two digits to the right of the decimal separator, and includes thousand separators. The sample here shows the default United States number formatting.
1,234,567.00 % "P" displays the number as a percentage (multiplied by 100) with formatting and the percent sign at the right of the number separated by a single space.
1.23E+04 "Scientific" displays the number in scientific notation with two decimal digits.
User-Defined Numeric Formats
A user-defined format expression for numbers can have from one to three sections separated by semicolons. If the Style argument of the Format function contains one of the predefined numeric formats, only one section is allowed.
| If you use | This is the result |
| One section only | The format expression applies to all values. |
| Two sections | The first section applies to positive values and zeros; the second applies to negative values. |
| Three sections | The first section applies to positive values, the second applies to negative values, and the third applies to zeros. |
Format Specifications
The following table identifies characters you can use to create user-defined number formats.
Format specification |
Description |
None |
Displays the number with no formatting. |
0 (zero character) |
Digit placeholder. Displays a digit or a zero. If the expression has a digit in the position where the zero appears in the format string, displays the digit; otherwise, displays a zero in that position. If the number has fewer digits than there are zeros (on either side of the decimal) in the format expression, displays leading or trailing zeros. If the number has more digits to the right of the decimal separator than there are zeros to the right of the decimal separator in the format expression, rounds the number to as many decimal places as there are zeros. If the number has more digits to the left of the decimal separator than there are zeros to the left of the decimal separator in the format expression, displays the extra digits without modification. |
# |
Digit placeholder. Displays a digit or nothing. If the expression has a digit in the position where the # character appears in the format string, displays the digit; otherwise, displays nothing in that position. This symbol works like the 0 digit placeholder, except that leading and trailing zeros aren't displayed if the number has fewer digits than there are # characters on either side of the decimal separator in the format expression. |
. (dot character) |
Decimal placeholder. The decimal placeholder determines how many digits are displayed to the left and right of the decimal separator. If the format expression contains only # characters to the left of this symbol; numbers smaller than 1 begin with a decimal separator. To display a leading zero displayed with fractional numbers, use zero as the first digit placeholder to the left of the decimal separator. In some locales, a comma is used as the decimal separator. The actual character used as a decimal placeholder in the formatted output depends on the number format recognized by your system. Thus, you should use the period as the decimal placeholder in your formats even if you are in a locale that uses a comma as a decimal placeholder. The formatted string will appear in the format correct for the locale. |
% |
Percent placeholder. Multiplies the expression by 100. The percent character (%) is inserted in the position where it appears in the format string. |
, (comma character) |
Thousand separator. The thousand separator separates thousands from hundreds within a number that has four or more places to the left of the decimal separator. Standard use of the thousand separator is specified if the format contains a thousand separator surrounded by digit placeholders (0 or #). A thousand separator immediately to the left of the decimal separator (whether or not a decimal is specified) or as the rightmost character in the string means "scale the number by dividing it by 1,000, rounding as needed." Numbers smaller than 1,000 but greater or equal to 500 are displayed as 1, and numbers smaller than 500 are displayed as 0. Two adjacent thousand separators in this position scale by a factor of 1 million, and an additional factor of 1,000 for each additional separator. Multiple separators in any position other than immediately to the left of the decimal separator or the rightmost position in the string are treated simply as specifying the use of a thousand separator. In some locales, a period is used as a thousand separator. The actual character used as the thousand separator in the formatted output depends on the Number Format recognized by your system. Thus, you should use the comma as the thousand separator in your formats even if you are in a locale that uses a period as a thousand separator. The formatted string will appear in the format correct for the locale. For example, consider the three following format strings: "#,0.", which uses the thousands separator to format the number 100 million as the string "100,000,000". "#0,.", which uses scaling by a factor of one thousand to format the number 100 million as the string "100000". "#,0,.", which uses the thousands separator and scaling by one thousand to format the number 100 million as the string "100,000". |
: (colon character) |
Time separator. In some locales, other characters may be used to represent the time separator. The time separator separates hours, minutes, and seconds when time values are formatted. The actual character used as the time separator in formatted output is determined by your system settings. |
/ (forward slash character) |
Date separator. In some locales, other characters may be used to represent the date separator. The date separator separates the day, month, and year when date values are formatted. The actual character used as the date separator in formatted output is determined by your system settings. |
E- , E+ , e- , e+ |
Scientific format. If the format expression contains at least one digit placeholder (0 or #) to the left of E-, E+, e-, or e+, the number is displayed in scientific format and E or e is inserted between the number and its exponent. The number of digit placeholders to the left determines the number of digits in the exponent. Use E- or e- to place a minus sign next to negative exponents. Use E+ or e+ to place a minus sign next to negative exponents and a plus sign next to positive exponents. You must also include digit placeholders to the right of this symbol to get correct formatting. |
-+$() |
Literal characters. These characters are displayed exactly as typed in the format string. To display a character other than one of those listed, precede it with a backslash (\) or enclose it in double quotation marks (" "). |
\ (backward slash character) |
Displays the next character in the format string. To display a character that has special meaning as a literal character, precede it with a backslash (\). The backslash itself isn't displayed. Using a backslash is the same as enclosing the next character in double quotation marks. To display a backslash, use two backslashes (\\). Examples of characters that can't be displayed as literal characters are the date-formatting and time-formatting characters (a, c, d, h, m, n, p, q, s, t, w, y, /, and :), the numeric-formatting characters (#, 0, %, E, e, comma, and period), and the string-formatting characters (@, &, <, >, and !). |
| "ABC" | Displays the string inside the double quotation marks (" "). To include a string in the style argument from within code, you must use Chr(34) to enclose the text (34 is the character code for a quotation mark (")). |
Remarks
If you include semicolons with nothing between them, the missing section is printed using the format of the positive value.
Example
The following table contains some sample format expressions for numbers. (These examples all assume that your system's locale setting is English-U.S.) The first column contains the format strings for the FORMAT function; the other columns contain the resulting output if the formatted data has the value given in the column headings.
| Format (Style) | "5" formatted as | "-5" formatted as | "0.5" formatted as | "0" formatted as |
| Zero-length string ("") | 5 | -5 | 0.5 | 0 |
| 0 | 5 | -5 | 1 | 0 |
| 0.00 | 5.00 | -5.00 | 0.50 | 0.00 |
| #,##0 | 5 | -5 | 1 | 0 |
$#,##0;($#,##0) |
$5 | ($5) | $1 | $0 |
$#,##0.00;($#,##0.00) |
$5.00 | ($5.00) | $0.50 | $0.00 |
| 0% | 500% | -500% | 50% | 0% |
| 0.00% | 500.00% | -500.00% | 50.00% | 0.00% |
0.00E+00 |
5.00+00 | -5.00E+00 | 5.00E-01 | 0.00E+00 |
0.00E-00 |
5.00E00 | -5.00E00 | 5.00E-01 | 0.00E00 |
"$#,##0;;\Z\e\r\o" |
$5 | $-5 | $1 | Zero |
Predefined Date/Time Formats
The following table identifies the predefined date and time format names. These may be used by name as the style argument for the FORMAT function:
| Format specification | Description |
"General Date", or "G" |
Displays a date and/or time. For example, 3/12/2008 11:07:31 AM. Date display is determined by your application's current culture value. |
"Long Date", "Medium Date", or "D" |
Displays a date according to your current culture's long date format. For example, Wednesday, March 12, 2008.
|
"Short Date", or "d" |
Displays a date using your current culture's short date format. For example, 3/12/2008. The d character displays the day in a user-defined date format. For more information, see User-Defined Date/Time Formats (Format Function). |
"Long Time", "Medium Time", or "T" |
Displays a time using your current culture's long time format; typically includes hours, minutes, seconds. For example, 11:07:31 AM. |
"Short Time" or "t" |
Displays a time using your current culture's short time format. For example, 11:07 AM. The t character displays AM or PM values for locales that use a 12-hour clock in a user-defined time format. For more information, see User-Defined Date/Time Formats (Format Function). |
"f" |
Displays the long date and short time according to your current culture's format. For example, Wednesday, March 12, 2008 11:07 AM. |
"F" |
Displays the long date and long time according to your current culture's format. For example, Wednesday, March 12, 2008 11:07:31 AM. |
"g" |
Displays the short date and short time according to your current culture's format. For example, 3/12/2008 11:07 AM. |
"M", "m" |
Displays the month and the day of a date. For example, March 12. The M character displays the month in a user-defined date format. The m character displays the minutes in a user-defined time format. For more information, see User-Defined Date/Time Formats (Format Function). |
"R", "r" |
Formats the date according to the RFC1123Pattern property. For example, Wed, 12 Mar 2008 11:07:31 GMT. The formatted date does not adjust the value of the date and time. You must adjust the Date/Time value to GMT before calling the Format function. |
"s" |
Formats the date and time as a sortable index. For example, 2008-03-12T11:07:31. The s character displays the seconds in a user-defined time format. For more information, see User-Defined Date/Time Formats (Format Function). |
"u" |
Formats the date and time as a GMT sortable index. For example, 2008-03-12 11:07:31Z. |
"U" |
Formats the date and time with the long date and long time as GMT. For example, Wednesday, March 12, 2008 6:07:31 PM. |
"Y", "y" |
Formats the date as the year and month. For example, March, 2008. The Y and y characters display the year in a user-defined date format. For more information, see User-Defined Date/Time Formats (Format Function). |
Remarks
Formatting strings are case sensitive. Different formatting can be obtained by using a different case. For example, when formatting a date value with the string "D" you get the date in the long format (according to your current locale). However, if you change the case to "d" you get the date in the short format. Also, unexpected results or an error might occur if the intended formatting does not match the case of any defined format string.
User-Defined Date/Time Formats
The following table shows characters you can use to create user-defined date/time formats.
| Format specification | Description |
| (:) | Time separator. In some locales, other characters may be used to represent the time separator. The time separator separates hours, minutes, and seconds when time values are formatted. The actual character that is used as the time separator in formatted output is determined by your application's current culture value. |
| (/) | Date separator. In some locales, other characters may be used to represent the date separator. The date separator separates the day, month, and year when date values are formatted. The actual character that is used as the date separator in formatted output is determined by your application's current culture. |
| (%) | Used to indicate that the following character should be read as a single-letter format without regard to any trailing letters. Also used to indicate that a single-letter format is read as a user-defined format. See what follows for additional details. |
| d | Displays the day as a number without a leading zero (for example, 1). Use %d if this is the only character in your user-defined numeric format. |
| dd | Displays the day as a number with a leading zero (for example, 01). |
| ddd | Displays the day as an abbreviation (for example, Sun). |
| dddd | Displays the day as a full name (for example, Sunday). |
| M | Displays the month as a number without a leading zero (for example, January is represented as 1). Use %M if this is the only character in your user-defined numeric format. |
| MM | Displays the month as a number with a leading zero (for example, 01/12/01). |
| MMM | Displays the month as an abbreviation (for example, Jan). |
| MMMM | Displays the month as a full month name (for example, January). |
| gg | Displays the period/era string (for example, A.D.). |
| h | Displays the hour as a number without leading zeros using the 12-hour clock (for example, 1:15:15 PM). Use %h if this is the only character in your user-defined numeric format. |
| hh | Displays the hour as a number with leading zeros using the 12-hour clock (for example, 01:15:15 PM). |
| H | Displays the hour as a number without leading zeros using the 24-hour clock (for example, 1:15:15). Use %H if this is the only character in your user-defined numeric format. |
| HH | Displays the hour as a number with leading zeros using the 24-hour clock (for example, 01:15:15). |
| m | Displays the minute as a number without leading zeros (for example, 12:1:15). Use %m if this is the only character in your user-defined numeric format. |
| mm | Displays the minute as a number with leading zeros (for example, 12:01:15). |
| s | Displays the second as a number without leading zeros (for example, 12:15:5). Use %s if this is the only character in your user-defined numeric format. |
| ss | Displays the second as a number with leading zeros (for example, 12:15:05). |
| f | Displays fractions of seconds. For example ff displays hundredths of seconds, whereas ffff displays ten-thousandths of seconds. You may use up to seven f symbols in your user-defined format. Use %f if this is the only character in your user-defined numeric format. |
| t | Uses the 12-hour clock and displays an uppercase A for any hour before noon; displays an uppercase P for any hour between noon and 11:59 P.M. Use %t if this is the only character in your user-defined numeric format. |
| tt | For locales that use a 12-hour clock, displays an uppercase AM with any hour before noon; displays an uppercase PM with any hour between noon and 11:59 P.M. For locales that use a 24-hour clock, displays nothing. |
| y | Displays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-defined numeric format. |
| yy | Displays the year in two-digit numeric format with a leading zero, if applicable. |
| yyy | Displays the year in four-digit numeric format. |
| yyyy | Displays the year in four-digit numeric format. |
| z | Displays the timezone offset without a leading zero (for example, -8). Use %z if this is the only character in your user-defined numeric format. |
| zz | Displays the timezone offset with a leading zero (for example, -08) |
| zzz | Displays the full timezone offset (for example, -08:00) |
Remarks
Formatting strings are case sensitive. Different formatting can be obtained by using a different case. For example, when formatting a date value with the string "D" you get the date in the long format (according to your current locale). However, if you change the case to "d" you get the date in the short format. Also, unexpected results or an error might occur if the intended formatting does not match the case of any defined format string.
Date/Time formatting uses the current user locale to determine the ultimate format of the string. For example, to format the date March 18, 1995, with the following format string "M/d/yyyy", if the user locale is set to the United States of America (en-us) the result is '3/12/1995', but if the user locale is set to Germany (de-de) the result is '18.03.1995'.
LEFT
Returns the specified number of characters from the start of a text string.
Whereas Microsoft Excel contains different functions for working with text in single-byte and double-byte character languages, PowerPivot for Excel works with Unicode and stores all characters as the same length; therefore, a single function is enough.
Syntax
LEFT(<text>, <num_chars>)
text. The text string containing the characters you want to extract, or a reference to a column that contains text.
num_chars. (optional) The number of characters you want LEFT to extract; if omitted, 1.
Returns a text string.
Example
The following example returns the first five characters of the company name in the column [ResellerName] and the first five letters of the geographical code in the column [GeographyKey] and concatenates them, to create an identifier.
=CONCATENATE(LEFT('Reseller'[ResellerName],LEFT(GeographyKey,3))
If the num_chars argument is a number that is larger than the number of characters available, the function returns the maximum characters available and does not raise an error. For example, the column [GeographyKey] contains numbers such as 1, 12 and 311; therefore the result also has variable length.
LEN
Returns the number of characters in a text string.
Whereas Microsoft Excel has different functions for working with single-byte and double-byte character languages, PowerPivot for Excel uses Unicode and stores all characters with the same length.
Therefore, LEN always counts each character as 1, no matter what the default language setting is.
If you use LEN with a column that contains non-text values, such as dates or Booleans, the function implicitly casts the value to text, using the current column format.
Syntax
LEN(<text>)
text. The text whose length you want to find, or a column that contains text. Spaces count as characters.
Returns a number indicating the number of characters in the text string (I8).
Example
The following formula sums the lengths of addresses in the columns, [AddressLine1] and [AddressLine2].
=LEN([AddressLine1])+LEN([AddressLine2)
LOWER
Converts all letters in a text string to lowercase.
Characters that are not letters are not changed. For example, the formula =LOWER("123ABC") returns 123abc.
Syntax
LOWER(<text>)
text. The text you want to convert to lowercase, or a reference to a column that contains text.
Returns text in lowercase.
Example
The following formula gets each row in the column, [ProductCode], and converts the value to all lowercase. Numbers in the column are not affected.
=LOWER('New Products'[ProductCode])
MID
Returns a string of characters from the middle of a text string, given a starting position and length.
Whereas Microsoft Excel has different functions for working with single-byte and double-byte characters languages, PowerPivot for Excel uses Unicode and stores all characters with the same length.
Syntax
MID(<text>, <start_num>, <num_chars>)
text. The text string from which you want to extract the characters, or a column that contains 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.
Returns a string of text of the specified length.
Example
The following examples return the same results, the first 5 letters of the column, [ResellerName]. The first example uses the fully qualified name of the column and specifies the starting point; the second example omits the table name and the parameter, num_chars.
=MID('Reseller'[ResellerName],5,1))
=MID([ResellerName,5])
The results are the same if you use the following formula:
=LEFT([ResellerName],5)
REPLACE
REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.
Whereas Microsoft Excel has different functions for use with single-byte and double-byte character languages, PowerPivot for Excel uses Unicode and therefore stores all characters as the same length.
Syntax
REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)
old_text. The string of text that contains the characters you want to replace, or a reference to a column that contains text.
start_num. The position of the character in old_text that you want to replace with new_text.
num_chars. The number of characters that you want to replace.
new_text. The replacement text for the specified characters in old_text.
Returns a text string.
Example
The following formula creates a new calculate column that replaces the first two characters of the product code in column, [ProductCode], with a new two-letter code, OB.
=REPLACE('New Products'[Product Code],1,2,"OB")
REPT
Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.
If number_times is 0 (zero), REPT returns a blank.
If number_times is not an integer, it is truncated.
The result of the REPT function cannot be longer than 32,767 characters, or REPT returns an error.
Syntax
REPT(<text>, <num_times>)
text. The text you want to repeat.
num_times. A positive number specifying the number of times to repeat text.
Returns a string containing the changes.
Example: Repeating Literal Strings
The following example returns the string, 85, repeated three times.
=REPT("85",3)
Example: Repeating Column Values
The following example returns the string in the column, [MyText], repeated for the number of times in the column, [MyNumber]. Because the formula extends for the entire column, the resulting string depends on the text and number value in each row.
=REPT([MyText],[MyNumber])
| MyText | MyNumber | CalculatedColumn1 |
| Text | 2 | TextText |
| Number | 0 | |
| 85 | 3 | 858585 |
RIGHT
RIGHT returns the last character or characters in a text string, based on the number of characters you specify.
RIGHT always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is.
Syntax
RIGHT(<text>, <num_chars>)
text. The text string that contains the characters you want to extract, or a reference to a column that contains text.
num_chars. (optional) The number of characters you want RIGHT to extract; is omitted, 1. You can also use a reference to a column that contains numbers.
If the column referenced does not contain text, it is implicitly cast as text.
Returns a text string containing the specified right-most characters.
Example: Returning a Fixed Number of Characters
The following formula returns the last two digits of the product code in the New Products table.
=RIGHT('New Products'[ProductCode],2)
Example: Using a Column Reference to Specify Character Count
The following formula returns a variable number of digits from the product code in the New Products table, depending on the number in the column, MyCount. If there is no value in the column, MyCount, or the value is a blank, RIGHT also returns a blank.
=RIGHT('New Products'[ProductCode],[MyCount])
SEARCH
Returns the number of the character at which a specific character or text string is first found, reading left to right. Search is case-sensitive.
By using this function, you can locate one text string within a second text string, and return the position where the first string starts.
You can use the SEARCH function to determine the location of a character or text string within another text string, and then use the MID function to return the text, or use the REPLACE function to change the text.
If the find_text cannot be found in within_text, the formula returns an error. This behavior is like Excel, which returns #VALUE if the substring is not found. Nulls in within_text will be interpreted as an empty string in this context.
Syntax
SEARCH(<find_text>, <within_text>, [start_num])
find_text. The text that you want to find.
within_text. The text in which you want to search for find_text, or a column containing text.
start_num. (optional) The character position in within_text at which you want to start searching. If omitted, 1.
Returns the number of the starting position of the first text string from the first character of the second text string.
Example: Search Within a String
The following formula finds the position of the letter "n" in the word "printer".
=SEARCH("n","printer")
The formula returns 4 because "n" is the fourth character in the word "printer."
Example: Search Within a Column
You can use a column reference as an argument to SEARCH. The following formula finds the position of the character "-" (hyphen) in the column, [PostalCode].
=SEARCH("-",[PostalCode])
The return result is a column of numbers, indicating the index position of the hyphen.
Example: Error Handling with SEARCH
The formula in the preceding example will fail if the search string is not found in every row of the source column. Therefore, the next example demonstrates how to use IFERROR with the SEARCH function, to ensure that a valid result is returned for every row.
The following formula finds the position of the character "-" within the column, and returns -1 if the string is not found.
= IFERROR(SEARCH("-",[PostalCode]),-1)
Note that the data type of the value that you use as an error output must match the data type of the non-error output type. In this case, you provide a numeric value to be output in case of an error because SEARCH returns an integer value.
However, you could also return a blank (empty string) by using BLANK() as the second argument to IFERROR.
SUBSTITUTE
Replaces existing text with new text in a text string.
Use the SUBSTITUTE function when you want to replace specific text in a text string; use the REPLACE function when you want to replace any text of variable length that occurs in a specific location in a text string.
The SUBSTITUTE function is case-sensitive. If case does not match between text and old_text, SUBSTITUTE will not replace the text.
Syntax
SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>)
text. The text in which you want to substitute characters, or a reference to a column containing text.
old_text. The existing text that you want to replace.
new_text. The text you want to replace old_text with.
instance_num. (optional) The occurrence of old_text you want to replace. If omitted, every instance of old_text is replaced.
Returns a string of text.
Example: Substitution Within a String
The following formula creates a copy of the column [Product Code] that substitutes the new product code NW for the old product code PA wherever it occurs in the column.
=SUBSTITUTE([Product Code], "NW", "PA")
TRIM
Removes all spaces from text except for single spaces between words.
Use TRIM on text that you have received from another application that may have irregular spacing.
The TRIM function was originally designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity, . By itself, the TRIM function does not remove this nonbreaking space character. For an example of how to trim both space characters from text, see Remove spaces and nonprinting characters from text.
Syntax
TRIM(<text>)
text. The text from which you want spaces removed, or a column that contains text.
Returns the string with spaces removed.
Example
The following formula creates a new string that does not have trailing white space.
=TRIM("A column with trailing spaces. ")
When you create the formula, the formula is propagated through the row just as you typed it, so that you see the original string in each formula and the results are not apparent. However, when the formula is evaluated the string is trimmed.
You can verify that the formula produces the correct result by checking the length of the calculated column created by the previous formula, as follows:
=LEN([Calculated Column 1])
UPPER
Converts a text string to all uppercase letters.
Syntax
UPPER (<text>)
text. The text you want converted to uppercase, or a reference to a column that contains text.
Returns the same text, in uppercase.
Example
The following formula converts the string in the column, [ProductCode], to all uppercase. Non-alphabetic characters are not affected.
=UPPER(['New Products'[Product Code])
VALUE
Converts a text string that represents a number to a number.
The value passed as the text parameter can be in any of the constant, number, date, or time formats recognized by Microsoft Excel and the PowerPivot Add-in. If text is not in one of these formats, an error is returned. For more information about PowerPivot data types, see Data Types Supported in PowerPivot Workbooks in the TechNet Library.
You do not generally need to use the VALUE function in a formula because the PowerPivot add-in implicitly converts text to numbers as necessary.
You can also use column references. For example, if you have a column that contains mixed number types, VALUE can be used to convert all values to a single numeric data type. However, if you use the VALUE function with a column that contains mixed numbers and text, the entire column is flagged with an error, because not all values in all rows can be converted to numbers.
Syntax
VALUE(<text>)
text. The text to be converted.
Returns the converted number (R8).
Example
The following formula converts the typed string, "3", into the numeric value 3.
=VALUE("3")
See Also
External Links