Text to column on Multiple Data

[Devika]

Hello,

On the below table I only wanted to create another column name 'ADML' which would only contain data starting with D not the no's in the bracket.

Please help me with the formula which would filter out the no's in bracket.

Trade Refs (Ours)	ADML
D345447 (000005)
D343792 (000005)
D527015 (002884)
D520288 (002884)
D520288 (002884)
I129702 (000005)
I129702 (000005)
D418531 (000005), D420345 (000005)
D428163 (000005), D428920 (000005)
D402199 (083004), D523515 (083004), D317365 (083004), D325464 (083004), D419502 (000005), D525641 (000005)

[binu_acs]

@Devika 

[Luke_C]

Hi @Devika 

Here's my approach:

1. Record ID to help group the data downstream
2. Split data to rows
3. Check if it starts with 'D' (a few start with I which you don't want based on your requirements) and populate ADML
4. Group records back together

[EN6924]

Assuming your dataset has 10 records as mentioned above, My solution -

[Devika]

this is the output dat im willing to get 

Trade Refs (Ours)	ADML
D345447 (000005)	D345447
D343792 (000005)	D343792
D527015 (002884)	D527015
D520288 (002884)	D520288
D520288 (002884)	D520288
I129702 (000005)	I129702
I129702 (000005)	I129702
D418531 (000005), D420345	D418531,D420345
D428163 (000005), D428920	D428163,D428920
D402199 (083004), D523515	D402199,D523515,D317365,D325464,D419502,D525641
(083004), D317365 (083004),
D325464 (083004), D419502
(000005), D525641 (000005)

[binu_acs]

@Devika any specific condition which has to consider when concatenate the rows, I can see the last 4 rows you concatenated in your output result.

[Devika]

Hello@binuacs,

Just modified the expected output 

Trade Refs (Ours)	ADML
D345447 (000005)	D345447
D343792 (000005)	D343792
D527015 (002884)	D527015
D520288 (002884)	D520288
D520288 (002884)	D520288
I129702 (000005)	I129702
I129702 (000005)	I129702
D418531 (000005), D420345	D418531,D420345
D428163 (000005), D428920	D428163,D428920
D402199 (083004), D523515(083004), D317365 (083004),D325464 (083004), D419502(000005), D525641 (000005)	D402199,D523515,D317365,D325464,D419502,D525641

[EN6924]

I did it like the following way, got accurate result as requested -

Please correct me if this can be done in more short way.

[binu_acs · Accepted solution]

@Devika