Excel Text Function Equivalents

For some time it’s bothered me that we don’t have full parity between Excel and Power Query functions.  In this post I’m going to look at four Excel text function equivalents, intended to allow an Excel user to just work with formulas as they’re used to.

=LEFT()

The first function is the LEFT function which, if implemented in M, would be:

(text as text,num_chars as number)=>
let
ReturnValue = Text.Start(text,num_chars)
in
ReturnValue

You’d call it like so:

=LEFT([Word],4)

And it would return the Excel text function equivalent as shown here.  (Excel’s output is in teh LEFT(x,4) column, and the Power Query LEFT function is shown in the pqLeft column.)

image

This one may not be a big deal, as it’s just a new function name, but it now works exactly as an Excel user would expect.  (Except for the fact that you MUST type the function name in uppercase.)

=RIGHT()

We can do the same thing with the RIGHT function:

(text as text,num_chars as number)=>
let
ReturnValue = Text.End(text,num_chars)
in
ReturnValue

And call it with:

=RIGHT([Word],4)

Returning:

image

=LEN()

This is yet another simple one:

(text as text)=>
let
ReturnValue = Text.Length(text)
in
ReturnValue

Called with:

=LEN([Word])

Returning:

image

Why I care about Excel text function equivalents?

Beyond allowing a user to just use the knowledge they’ve been investing in for years, it’s the next function that makes a difference.  With the MID function we have a bunch of things that are different, and to replicate our expectations we need to do some manipulation and conditional testing.

=MID()

This is what it takes to replicate the MID() function in Power Query:

(text as text,start_num as number,num_chars as number)=>
let

tLength = Text.Length(text),
StartCorrected = if start_num>tLength then -1 else start_num-1,
ReturnCorrected = if (StartCorrected+num_chars) > tLength then tLength - start_num + 1 else num_chars,
ReturnValue = if StartCorrected >-1
then Text.Range(text,StartCorrected,ReturnCorrected)
else null
in
ReturnValue

That’s a fairly big pain to just knock up in a couple of seconds!  But once done, it’s easy to use:

=MID([Word],5,4)

And it returns results consistent to Excel as well:

image

Final Thoughts

I really wish these functions were built under and consistent with Excel’s function names to begin with.  While we can replicate their form and function, it takes a lot of work to create/debug and implement them in a power query solution.  In addition, some of the functions are VERY difficult to built.  But regardless, at least we can.

Share:

Facebook
Twitter
LinkedIn

21 thoughts on “Excel Text Function Equivalents

  1. Pingback: Excel Roundup 20150608 « Contextures Blog

  2. Bear in mind that Microsoft's focus is to towards SQL Server rather then Excel and that's why we have to live with the inconsistency.

  3. I think it's even a bit more disparate than that, Dennis. I've head that a lot of the key players came from a Visual Studio environment. That would explain some of the more .NET focused styles.

  4. Hi Ken,
    If you defined previous functions (LEFT, RIGHT, LEN) then you can use its, inside definition of MID.
    MID = (text as any,start_num as number,num_chars as number)=>
    let
    textOk = Text.From(text),
    ReturnValue = try if LEN(textOk)
    let
    textOk = Text.From(text),
    ReturnValue = try if Text.Length(textOk) < start_num then null
    else
    Text.Start(Text.End(textOk, Text.Length(textOk)-start_num+1),num_chars) otherwise null
    in
    ReturnValue

    Regards 🙂

  5. o, no... seems that your WordPress cut some parts of code (big parts) :-((
    So, code in my previous post is completely wrong :-((

  6. Hi there,
    you could also use alternative methods to cater for the conditions that will result in a more "Excel-like" Syntax:

    (text as text,start_num as number,num_chars as number)=>

    let

    Mid = try Text.Range(text, start_num-1, List.Min({num_chars, Text.Length(text)-(start_num-1)})) otherwise ""

    in
    Mid

  7. Hi There,

    Thanks for these.

    Any idea of an equivalent of the type() function?

    I've used Value.Type ([ColumnName]) but it returns results as hyperlinks when I'd like the value to be displayed.

    Thanks

    Tibo

  8. Hi Tibo,

    You're correct, Value.Type just doesn't want to play nicely. I'm not sure what's up with that, but will ping Microsoft on it.

  9. I have just used a combination of PositionOf and List functions in place of mid to find a string like "ROOM 104", "ROOM 1.1" or "ROOM 1" etc.

    So basically find the position of ROOM + its length. Get a list of all the occurrences of spaces. Find the values in the list greater than the text string then use the first two of those in Text.Range as the start and end values.

  10. In the excel FIND formula, you can add in the syntax a start position. In the M language text.positionof ... this is not possible?
    Is there a workaround?

  11. What is the difference between Text.Range and Text.Middle? I've been using Text.Middle as the Excel equivalent to MID() until I saw your M is for Data Monkey book. They seem to do the same thing.

  12. Oh, just figured it out.
    = Text.Middle("Text",0,8) will work. It returns Text.
    = Text.Range("Text",0,8) will error out as there are only 4 chars.

    So I can skip pages 150-152 of your book. 😉
    Text.Middle() must have come out fairly recently.

  13. Is there a similar function TEXT (excel) in POWERQUERY? I want to reacreate =Text(column,"0000") . Where column has numbers or text with this values 2,3,7,22,345, V021. The target of this is change the number into this format 0002,0003,0007,0022,0345,V021

    I really appreciate your help

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts