String Parsing Question

[#1]

Dear Community,

I am receiving data with identifying information in 1 cell.

Names Default to Here(1);

dt = New Table("Sample",
add rows(2),
	New Column( "Field_9630", Character, "Nominal" )
);

dt:Field_9630 << Set Values({"Customer: Mr A LoyalProduct: Cabinet HardwareRegion: Suburban New Dev", "Customer: Jenifer B Ashley LeighProduct: LumberRegion: Suburban Existing"});

The target is to produce three columns - Customer, Product, Region

dt2 = New Table( "Sample 2",
	Add Rows( 2 ),
	New Column( "Field_9630",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"Customer: Mr A LoyalProduct: Cabinet HardwareRegion: Suburban New Dev",
			"Customer: Jenifer B Ashley LeighProduct: LumberRegion: Suburban Existing"
			}
		),
		Set Display Width( 132 )
	),
	New Column( "Customer",
		Character,
		"Nominal",
		Set Values( {"Mr A Loyal", "Jenifer B Ashley Leigh"} )
	),
	New Column( "Product",
		Character,
		"Nominal",
		Set Values( {"Cabinet Hardware", "Lumber"} )
	),
	New Column( "Region",
		Character,
		"Nominal",
		Set Values( {"Suburban New Dev", "Suburban Existing"} )
	)
);

Many thanks in advance!

Sincerely,

MG

[#2 · Accepted solution]

Hi,

It is always challenging to parse strings when you do not have clear delimiters.

Assuming that the input string will always contain the keywords "Customer:", "Product:", and "Region:", you can use the approach below:

1. Replace the keywords with "," using the SUBSTITUTE() function
2. Parse the relevant information using the WORD() function
3. Clean up the resulting strings with the TRIM() function.

New Table( "Sample_PARSED",
	Add Rows( 2 ),
	New Column( "Field_9630",
		Character,
		"Nominal",
		Set Values(
			{"Customer: Mr A LoyalProduct: Cabinet HardwareRegion: Suburban New Dev",
			"Customer: Jenifer B Ashley LeighProduct: LumberRegion: Suburban Existing"
			}
		),
		Set Display Width( 726 )
	),
	New Column( "TEMP",
		Character,
		"Nominal",
		Formula(
			Substitute( :Field_9630,
				"Customer:", ",",
				"Product:", ",",
				"Region:", ","
			)
		)
	),
	New Column( "Column 2",
		Character,
		"Nominal",
		Formula( Trim( Word( 1, :TEMP, "," ) ) ),
		Set Display Width( 157 )
	),
	New Column( "Column 3",
		Character,
		"Nominal",
		Formula( Trim( Word( 2, :TEMP, "," ) ) )
	),
	New Column( "Column 4",
		Character,
		"Nominal",
		Formula( Trim( Word( 3, :TEMP, "," ) ) ),
		Set Display Width( 200 )
	)
)

I hope it helps.

Best,

TS

[#3]

@Thierry_S Thanks for the quick reply and solution. Much appreciated!