Parse question

Hi folks,

What is the simplest and easiest way to remove the "0.data." and move the numbers to a separate column?

thank you!

Name

0.data.1.figi

0.data.2.name

0.data.3.ticker

0.data.4.exchCode

0.data.5.compositeFIGI

0.data.6.securityType

0.data.7.marketSector

0.data.8.shareClassFIGI

0.data.9.securityType2

0.data.10.securityDescription

expected

Accepted answers

@jillv , Here are 2 ways to do it with regex and text to columns tool. You can try out any you consider simple.

One way of doing this

Parse by RegEx for you:

This can be done like this as well without writing any expression,I think this is simplest in this case

@jillv , Here are 2 ways to do it with regex and text to columns tool. You can try out any you consider simple.

@jillv
The format you have is a typical JSON format, and you only have the JSON_Name column and you should have also JSON_ValueString column.

We have a weekly challenge which has shown how to parse the data. 😁