Reg EX

[Navyasree123]

Hi All , Hope all are doing Good

"I have a dataset with information stored in a column, and I need to extract specific values using a regular expression. How can I achieve this using [REG EX Tool]? Specifically, I want to extract [LedgerNames and Company codes ] from the [LedgerMapping]. Can you please guide me on the steps or the regex pattern I should use?"

I have provided the information in the below attachment Sheet 1 is input And sheet2 is output .

ledgerMapping

[{'ledgerName': 'Amazon.it','Amazon.com', 'companyCodes': ['4W', '5W', '6W', 'B100', 'B573', 'B7', 'B896', 'B951', 'BAIT', 'BB34', 'BC36', 'BD34', 'BM', 'E6', 'EW', 'F7', 'F8', 'F9', 'FZ', 'JW', 'KW', 'MT', 'NI', 'V2', 'X6IT']}]

output

ledgerName	CompanyCodes
Amazon.in	4W
Amazon.com	5W
	6W
	B100
	B573
	B7
	B896
	B951
	BAIT
	BB34
	BC36
	BD34
	BM
	E6
	EW
	F7
	F8
	F9
	FZ
	JW
	KW
	MT
	NI
	V2
	X6IT

[binuacs]

@Navyasree123 This can be done using the JSON parse tool, but make sure your input data is proper JSON format

[Amol_Telore · Accepted solution]

Hey @Navyasree123 ,

You can achieve this using Regex and Pivot tools.

[Navyasree123]

Thank you All!!!

[CoG]

If you have been helped by the suggestions above, please kindly mark them as solutions to give the authors credit and help others find the solutions to their problems!

Happy Solving!!!