Text functions – Excel & Power Query comparision

For many Excel users, the transition to Power Query can be difficult. Having spent years working with Excel’s functions, moving to Power Query seems like an alien environment.

The good news is that many of Excel’s text functions have equivalent functions in Power Query.

The purpose of this post is to provide a reference to find and apply the equivalent Power Query text functions. It is not an exhaustive list but includes the functions a user will most likely use.

Table of Contents

• Using Power Query text functions
• User interface
• Custom column
• Formula bar
• Advanced editor
• Column by examples
• Functions
• CHAR / UNICHAR
• CLEAN
• CODE / UNICODE
• CONCAT / CONCATENATE
• FIND
• LEFT
• LEN
• LOWER
• MID
• PROPER
• REPLACE
• REPT
• RIGHT
• SEARCH
• SUBSTITUTE
• TEXT
• TEXTAFTER
• TEXTBEFORE
• TEXTJOIN
• TRIM
• UPPER
• Conclusion

Using Power Query text functions

Power Query text functions can be used in various places; so, let’s start our exploration here.

User interface

Many text functions, but not all, are available as individual transformations within the user interface. We can find them in the Add Column > From Text and Transform > Text Columns sections of the ribbon.

Add Column > From Text

Transform > Text Columns

The options in the user interface are individual transformations.

For more advanced transformations that combine functions, we can use a custom column, the formula bar, or the advanced editor.

Custom column

A custom column allows us to add a calculation for every row of the Table.

From the ribbon, click Add Column > Custom Column to display the Custom Column dialog box.

The example above demonstrates the Text.Start function (equivalent to Excel’s LEFT function) to extract the first characters from a text value.

After clicking OK, this creates a value for every row of the Table.

Formula bar

The formula bar displays the M code for each transformation step. For applying more advanced skills, we use the formula bar to either write formulas from scratch or edit existing formulas.

The example above shows the formula generated for the custom column created above.

Advanced editor

The advanced editor displays the M code for an entire query; this includes every formula for every transformation.

To open the advanced editor, click View > Advanced Editor.

The screenshot above displays the code for the entire query. The highlighted code is the Text.Start function used in the custom column section.

Column by examples

The last place we may see Power Query’s text functions is using Add Column > Column From Examples.

While we do not write the formulas ourselves, Power Query identifies patterns and provides a formula that achieves the required result.

As shown in the screenshot, Power Query determined Text.Start is the formula to achieve the result shown in the First Characters column.

With this method, we may not write the formula ourselves, but understanding the structure helps to ensure that Power Query’s suggestion is correct.

Functions

Now, it’s time to look at the functions. They are presented below in alphabetical order by Excel function name.

---

CHAR / UNICHAR

	Excel	Power Query
Name:	CHAR / UNICHAR	Character.FromNumber
Description:	Returns the character specified by the code number	Converts a number to a text character.
Syntax:	CHAR(number) UNICHAR(text)	Character.FromNumber(number)
Example:	=CHAR(65)	=Character.FromNumber(65)
Result:	“A”	“A”

• CHAR includes character numbers from 1 to 255; any character codes greater than 255 require the UNICHAR function.

---

CLEAN

	Excel	Power Query
Name:	CLEAN	Text.Clean
Description:	Returns a text value with all control characters removed.	Removes all nonprintable characters from text.
Syntax:	CLEAN(text)	Text.Clean(text)
Example:	=CLEAN(“Example text”)	=Text.Clean(“Example text”)
Result:	“Exampletext”	“Exampletext”

---

CODE / UNICODE

	Excel	Power Query
Name:	CODE / UNICODE	Character.ToNumber
Description:	Returns a numeric code for the first character in a text string.	Converts a character to a number value.
Syntax:	CODE(text) UNICODE(text)	Character.ToNumber(text)
Example:	=CODE(“A”)	=Character.ToNumber(“A”)
Result:	65	65

• CODE includes the standard ASCII characters from numbers 1 to 255, any character codes greater than 255 require the UNICODE function.

---

CONCAT / CONCATENATE

	Excel	Power Query
Name:	CONCAT	Text.Combine
Description:	Combines text from multiple ranges and/or strings.	Returns a text value that is the result of joining all text values.
Syntax:	CONCAT(text1, [text2],…) CONCATENATE(text1, [text2],…)	Text.Combine(texts, [separator])
Example:	=CONCAT(“Example”, ” “, “text”)	=Text.Combine({“Example”, ” “, “text”})
Result:	“Example text”	“Example text”

• If a separator is required between each word, look at the TEXTJOIN function.
• The & character is an alternative to using a function. & combines text strings in both Excel and Power Query.
• CONCAT (introduced in Excel 2019) is a more flexible replacement for the CONCATENATE function. CONCATENATE still exists for compatibility with previous versions.

---

FIND

	Excel	Power Query
Name:	FIND	Text.PositionOf
Description:	Finds the start position of a text value within another (case-sensitive)	Returns the occurrence of substring in a string and returns its position.
Syntax:	FIND(find_text, within_text, [start_num])	Text.PositionOf(text, substring, [occurrence], [comparer])
Example:	=FIND(“mp”, “Example text”)	=Text.PositionOf(“Example text”, “mp”)
Result:	4	3

• FIND is case insensitive. For a case-insensitive version, check out the SEARCH function.
• Power Query returns the zero-based position; therefore, the results between Excel and Power Query appear different.
• Power Query’s [occurrence] argument is zero-based. So, to find the second instance, the [occurrence] will be 1.
• Power Query has an additional function, Text.PositionOfAny, which allows a list of characters to be used as the basis of the search.

---

LEFT

	Excel	Power Query
Name:	LEFT	Text.Start
Description:	Returns the leftmost characters from a text value.	Returns the count of characters from the start of a text value.
Syntax:	LEFT(text, [num_chars])	Text.Start(text, count)
Example:	=LEFT(“Example text”, 7)	=Text.Start(“Example text”, 7)
Result:	“Example”	“Example”

Notes:

• The [num_chars] argument is optional. If excluded, only 1 character is returned.
• Power Query also has the Text.StartsWith function, which returns True/False when the text starts with a specified string.
  Syntax: Text.StartsWith(text,substring,[comparer])
  Example: Text.StartsWith(“Example Text”,”Example”)
  Result: True

---

LEN

	Excel	Power Query
Name:	LEN	Text.Length
Description:	Returns the number of characters in a text string.	Returns the number of characters in a text value.
Syntax:	LEN(text)	Text.Length(text)
Example:	=LEN(“Example text”)	=Text.Length(“Example text”)
Result:	12	12

---

LOWER

	Excel	Power Query
Name:	LOWER	Text.Lower
Description:	Converts text to lowercase.	Returns the lowercase of a text value.
Syntax:	LOWER(text)	Text.Lower(text, [culture])
Example:	=LOWER(“Example text”)	=Text.Lower(“Example text”)
Result:	“example text”	“example text”

Notes:

• The optional [culture] argument within Text.Lower is not covered in this post.

---

MID

	Excel	Power Query
Name:	MID	Text.Middle
Description:	Returns a specific number of characters from a text string starting at a specified position.	Returns a substring up to a specific length.
Syntax:	MID(text, start_num, num_chars)	Text.Middle(text, start, count)
Example:	=MID(“Example text”, 4, 2)	=Text.Middle(“Example text”, 3, 2)
Result:	“mp”	“mp”

Note:

• The start argument for Text.Middle is zero-based. Therefore, 0 for the start argument in Text.Middle is equivalent to 1 for start_num argument in MID.
• In Power Query, Text.Range is an alternative to Text.Middle. The difference is that Text.Range returns an error when there are insufficient characters to return a value.
• If we only require a single character, a Power Query alternative is Text.At:
  Syntax: Text.At(text,index)

---

PROPER

	Excel	Power Query
Name:	PROPER	Text.Proper
Description:	Capitalizes the first letter in each word of a text value	Capitalizes the first letter in each word of a text value.
Syntax:	PROPER(text)	Text.Proper(text, [culture])
Example:	=PROPER(“Example text”)	=Text.Proper(“Example text”)
Result:	“Example Text”	“Example Text”

Notes:

• The optional [culture] argument within Text.Proper is not covered in this post.

---

REPLACE

	Excel	Power Query
Name:	REPLACE	Text.Replace
Description:	Replaces characters within text.	Replaces length characters in a text value starting at a zero-based offset with the new text value.
Syntax:	REPLACE(old_text, start_num, num_chars, new_text)	Text.ReplaceRange(text, offset, count, newText)
Example:	=REPLACE(“Example text”, 5, 3, “ination”)	=Text.ReplaceRange(“Example text”, 4, 3, “ination”)
Result:	“Examination text”	“Examination text”

Notes:

• The offset argument in Power Query’s Text.Replace is zero-based.
• To remove text, rather than replace it, we can also use Text.RemoveRange in Power Query.
  Syntax: Text.RemoveRange(text, offset, count)

---

REPT

	Excel	Power Query
Name:	REPT	Text.Repeat
Description:	Repeats text a given number of times.	Returns a text value composed of the input text value repeated a number of times.
Syntax:	REPT(text, number_times)	Text.Repeat(text, count)
Example:	=REPT(“Example”, 3)	=Text.Repeat(“Example”, 3)
Result:	“ExampleExampleExample”	“ExampleExampleExample”

---

RIGHT

	Excel	Power Query
Name:	RIGHT	Text.End
Description:	Returns the rightmost characters from a text value.	Returns the count of characters from the end of a text value.
Syntax:	RIGHT(text, [num_chars])	Text.End(text, count)
Example:	=RIGHT(“Example text”, 4)	=Text.End(“Example text”, 4)
Result:	“text”	“text”

Notes:

• The [num_chars] argument is optional. If excluded, only 1 character is returned.
• Power Query also has the Text.EndsWith function, which returns True/False when the text ends with a specified string.
  Syntax: Text.EndsWith(text,substring,[comparer])
  Example: Text.EndsWith(“Example Text”,”Text”)
  Result: True

---

SEARCH

	Excel	Power Query
Name:	SEARCH	Text.PositionOf
Description:	Finds the start position of a text value within another (case-insensitive)	Returns the occurrence of substring in a string and returns its position.
Syntax:	SEARCH(find_text, within_text, [start_num])	Text.PositionOf(text, substring, [occurrence], [comparer])
Example:	=SEARCH(“mp”, “Example Text”)	=Text.PositionOf(“Example Text”, “mp”, 0, Comparer.OrdinalIgnoreCase)
Result:	4	3

• SEARCH is case insensitive. For a case sensitive version, check out the FIND function.
• Look at the FIND function for additional usage notes.

---

SUBSTITUTE

	Excel	Power Query
Name:	SUBSTITUTE	Text.Replace
Description:	Substitutes new text for old text in a text string.	Replaces all occurrences of a substring with a new text value.
Syntax:	SUBSTITUTE(text, old_text, new_text, [instance_num])	Text.Replace(text, old, new)
Example:	=SUBSTITUTE(“Example text”, “Example”, “New”)	=Text.Replace(“Example text”, “Example”, “New”)
Result:	“New text”	“New text”

Notes:

• SUBSTITUTE has an optional [instance_num] argument which is not available in Power Query.

---

TEXT

	Excel	Power Query
Name:	TEXT
Description:	Formats a number and converts it to text
Syntax:	=TEXT(value, format_text)
Example:	=TEXT(50, “0000”)
Result:	0050

Notes:

• There is no direct equivalent to the TEXT function in Power Query. However, combining many text functions together, can create a similar effect. Look at the following functions for options:
  • Text.From – Returns the text representation of a number, date, time, datetime, datetimezone, logical, duration or binary value.
  • Text.Format – Returns formatted text that is created by applying arguments from a list or record to a format string.
  • Text.PadStart – Returns a text value padded at the beginning to make it at least a specified length of characters.
  • Text.PadEnd – Returns a text value padded at the end to make it at least a specified length of characters.

---

TEXTAFTER

	Excel	Power Query
Name:	TEXTAFTER	Text.AfterDelimiter
Description:	Returns text that occurs after given character or string	Returns the portion of text after the specified delimiter.
Syntax:	TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])	Text.AfterDelimiter(text, delimiter, [index])
Example:	=TEXTAFTER(“Example text”,” “)	=Text.AfterDelimiter(“Example text”, ” “)
Result:	“text”	“text”

Notes:

• Power Query uses a zero-base. Therefore, [index] of 0 is equivalent to [instance_num] of 1.
• Both functions provide optional arguments to start at the end of the string.
• [match_mode] determines whether the delimiter search is case sensitive. (0 = Case sensitive, 1 = Case insensitive). Power Query is case sensitive.
• [match_end] treats the end of the text as a delimiter. Power Query does not have an equivalent argument.
• [if_not_found] is the value to return if no matching delimiter is found. Power Query does not have an equivalent argument.
• Power Query has the Text.BetweenDelimiters function to provide the text between two delimiters. In Excel, this can be created through a combination of TEXTAFTER and TEXTBEFORE.

---

TEXTBEFORE

	Excel	Power Query
Name:	TEXTBEFORE	Text.BeforeDelimiter
Description:	Returns text that occurs before given character or string	Returns the portion of text before the specified delimiter.
Syntax:	TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])	Text.BeforeDelimiter(text, delimiter [index])
Example:	=TEXTBEFORE(“Example text”,” “)	=Text.BeforeDelimiter(“Example text”, ” “)
Result:	“Example”	“Example”

Notes:

• See TEXTAFTER for similar examples and usage notes.

---

TEXTJOIN

	Excel	Power Query
Name:	TEXTJOIN	Text.Combine
Description:	Combines the text from multiple ranges and/or strings.	Returns a text value that is the result of joining all text values with each value separated by a separator.
Syntax:	TEXTJOIN(delimiter, ignore_empty, text1, [text2]…)	Text.Combine(texts, [separator])
Example:	=TEXTJOIN(“,”, FALSE, “Example”, “text”)	=Text.Combine({“Example”, “text”}, “,”)
Result:	“Example,text”	“Example,text”

• The CONCAT function combines text without using a separator.

---

TRIM

	Excel	Power Query
Name:	TRIM	Text.Trim
Description:	Removes spaces from text.	Returns the result of removing all leading and trailing whitespace from text value.
Syntax:	TRIM(text)	Text.Trim(text, [trim])
Example:	=TRIM(” Example text “)	=Text.Trim(” Example text “)
Result:	“Example Text”	“Example text”

• The Power Query version of Trim does not remove white space from the middle of text values. Check out this post to replicate Excel’s TRIM function in Power Query.
• Text.Trim includes an optional [trim] argument to enable trimming of other characters.
  For example, to trim hyphens use “-” as the [trim] argument.
  Example: Text.Trim( “—Example Text—“, “-” )
  Result: “Example Text”
• Power Query has additional trim functions:
  • Text.TrimEnd – Returns the result of removing all trailing whitespace from text value
    Syntax: Text.TrimEnd (text, [trim])
  • Text.TrimStart – Returns the result of removing all leading whitespace from text value
    Syntax: Text.TrimStart (text, [trim])

---

UPPER

	Excel	Power Query
Name:	UPPER	Text.Upper
Description:	Converts text to uppercase.	Returns the uppercase of a text value.
Syntax:	UPPER(text)	Text.Upper(text, [culture])
Example:	=UPPER(“Example text”)	=Text.Upper(“Example text”)
Result:	“EXAMPLE TEXT”	“EXAMPLE TEXT”

Notes:

• The optional [culture] argument within Text.Upper is not covered in this post.

---

Conclusion

For the majority of Excel text functions, there is an equivalent Power Query text function.

However, there are important differences to be aware of:

• Many Power Query arguments and results are zero-based.
• A function may have additional arguments which are not available in the equivalent function.
• The order of arguments may differ between
• Power Query, by default, is case-sensitive, Excel is case-insensitive.

Want to know more about Power Query text functions? Check out this post on Gorilla.bi

Related Posts:

• How to use Power Query Custom Functions
• Power Query If statement: nested ifs & multiple conditions
• Power Query formulas (how to use them and pitfalls to avoid)

---

What next?

Discover how you can automate your work with our Excel courses and tools.