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)

Text to column on Multiple Data

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.

Workflow

Here's my approach:

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

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

this is the output dat im willing to get

@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.

Just modified the expected output

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

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