Excel Advanced Editor "SplitTextByDelimiter minus 3 characters"

[JimmyWilliams]

Hi Guys,

I am new to M Language (Excel Advanced Editor).

I am trying to get commodity data from a website "https://tradingeconomics.com/commodities" and put it into an excel sheet.

I want to split the "Energy" Column into 2 columns; commodity and the units of the commodity, like the below example:
Crude Oil ; USD/Bbl
Brent ; USB/Bbl
...


the solution I would like is "Split Column by Delimiter -3 characters" is this possible? How would I write this in the advanced editor.
If I did it this way, the column would be split correctly regardless if it is "USD" or "CNY" or "EUR", etc.

Any ideas?


My attempt using the Splitter.SplitTextByDelimiter

Please Login or Register  to view this content.

[oeldere]

HOW TO ATTACH YOUR SAMPLE WORKBOOK:

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[romperstomper]

It looks like you actually need to use a different approach since you aren't splitting at a delimiter. You can find the forward slash and then use that position to extract the data up to 3 chars before and after that.