Concatenating carriage returned values from 3 columns

[Legacy 226937]

Hey,

I have 100s of rows with cells similar to the table as shown below. Each field is described by it's coded value and it's related value description - linked by number. Each value in the coded value and value description field are separate by a carriage return (char 10) - I'd like to concatenate them to be as shown in the Desired Result column.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Field ID[/TD]
[TD]Name of Field[/TD]
[TD]Coded Values[/TD]
[TD]Friendly Value[/TD]
[TD]Value Definition[/TD]
[TD]Desired Result:
#) %Friendly Value% (%Coded Value%) - %Value Definition%[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Field A[/TD]
[TD]1) Y
2) N[/TD]
[TD]1) Yes
2) No[/TD]
[TD][/TD]
[TD]1) Yes (Y)
2) No (N)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Field B[/TD]
[TD]1) Abc
2) Bcd
3) EfA[/TD]
[TD]1) Alpha Bravo Charlie
2) Bravo Charlie Delta
3) Echo Frank Alpha[/TD]
[TD]1) Definition blah blah
2) Definition blah blah
3) Definition blah blah[/TD]
[TD]1) description (Abc) - Definition blah blah
2) description (Bcd) - Definition blah blah
3) description (Bcd) - Definition blah blah[/TD]
[/TR]
</tbody>[/TABLE]

The number of values per cell vary - there could be no value or 50 values.

I'm not sure how to go about this exactly. I'm also welcome to suggestions. I was also thinking of separating values into individual rows per value then concatenating per row but then the challenge is how do I merge it again to look like the above. I would prefer this method if someone knows how.

Any help would be appreciated!

Thanks,

Jason

 

[Legacy 226937]

To clarify, field ID 1 is 1 row, field ID 2 is another row. The values are stored and separated by carriage return within the cell.