Expression.Error: The name 'Text.BeforeDelimiter' wasn't recognized. Excel 2016

I have an excel "Add Column" from Power Query editor. My data looks like this,

26567-5698 51254-5458 6954-1545 45415 56541 5621 ..

Some have 4 digits before - and some have 5 digits before -. For those values that have - in between, I like to extract the first part (before delimiter).

-

I tried the following,

if Text.Length(Text.BeforeDelimiter([MyCol], "-")) = 4 then "0" & Text.Start([MyCol],4) else if Text.Length(Text.BeforeDelimiter([MyCol], "-")) = 5 then Text.Start([MyCol],5) else [MyCol]

If the length before delimiter I am adding a 0 and first 4 digits. Otherwise, I want the first 5 digits.

0

When I do the above, I get the following error:

Expression.Error: The name 'Text.BeforeDelimiter' wasn't recognized.
Make sure it's spelled correctly.

Here is the documentation I am following.

I am using Excel 2016. I have been searching and could not find anything related to this. Any help would be appreciated.

Possible duplicate of Having trouble with Text.BeforeDelimiter
– Alexis Olson
Sep 5 '18 at 21:12

@AlexisOlson Any alternative ways to reach my goal in power query?
– user9431057
Sep 5 '18 at 21:14

There are other ways, but are you unable to update your Excel? This should be rolled into Excel 2016 through normal updates by now.
– Wedge
Sep 5 '18 at 21:23

@Wedge "This should be rolled into Excel 2016 through normal updates by now" I am using Excel 2016 and still an issue for now!
– user9431057
Sep 5 '18 at 21:26

@Wedge would you be able to give a hint on other ways please? I was thinking of splitting the text by delimiter, but having a hard time taking the first part after splitting
– user9431057
Sep 5 '18 at 21:27

1 Answer
1

You can do a workaround with other text functions.

Left = try Text.Start([MyCol], Text.PositionOf([MyCol], "-")) otherwise [MyCol] Right = try Text.Range([MyCol], Text.Length([Left]) + 1) otherwise null

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt