How can I concatenate the rows in excel

Hi All,

I want to concatenate the number of rows which is dynamic i.e sometimes I need to concatenate rows
Input :
Column A
item 1
item 2

item n

in columnA some time the rows will be 2 ,sometime 3 rows , 4 row s etc

I want to concatenate like item1+ “" + item2 if the number of rows is 2
I want to concatenate like item1+ "
” + item2+ “" + item3 if the number of rows is 3
I want to concatenate like item1+ "
” + item2+ “" + item3+ "” + item4 if the number of rows is 4
but it should concatenate between 3 astrik symbol “***”.

Can someone please do needful to do this??


Hi @HeartCatcher

Could you clarify more. Please provide the sample input and output.

Modd.xlsx (8.1 KB)

Hi @HeartCatcher

You can proceed like this



can you check:

strFlatten = String.Join(“|”, yourDTVAr.AsEnumerable.Select(Function (x) x(ColNameOrIndex).toString.Trim))


from the excel I want merger 2+A3 like
“FULL 14 POSITION BCBSNC MEMBER ID REQUIRED.”+***+“RRQ Valid Referral Format Required”

the final output

strFlatten = String.Join("***" yourDTVAr.AsEnumerable.Select(Function (x) x(ColNameOrIndex).toString.Trim))

it is dynamic some time I need to merge A2+A3, sometime A2+A3+A4 etc and so on

do one thing: respecify your requirements in complete version. Share with us samples and its output descriptions. Use the </> format button from editor, when sharing snippets influencing MD Code from editor e.g. * making italic

code is concatenating dynamic the row/col values maybe you can adapt by following:

strFlatten =

String.Join("***" & Environment.NewLine, yourDTVAr.AsEnumerable.Select(Function (x) x(ColNameOrIndex).toString.Trim))

Hi @HeartCatcher

you can try this


dt_Output =

	From i In Enumerable.Range(0, dt_Data.Rows.Count)
	Let s = String.Join(" *** ", dt_Data.AsEnumerable.Take(i+1).Select(Function(r) r("Column1").ToString))
	Select dt_Data.LoadDataRow({s}, True)



Demo.xaml (8.7 KB)

1 Like

Please refer different scenario

If A coulmn contains A,B,C,D(row1,row2,row3,row4) then output should be
in the same if the input is x,y,z then output should be x
y***Z etc
instead of multiple rowsI want to make single row

Modd (1).xlsx (8.0 KB)
Can you please implement on the excel please and send the code


try this

Demo.xaml (7.0 KB)

Hi @HeartCatcher ,
New (8.8 KB)

This will give your desired output in logs with high accuracy.

Thanks & Regards,
Shubham Dutta

this is correct can you please the final output in excel,
I mean write into an excel instead of showing log message

Hi @HeartCatcher ,
New (15.0 KB)

Your output will be in output.xlsx.

Thanks & Regards,
Shubham Dutta

case can be solved as following

we do not recommend to reimplement a String.Join. When a non-Linq approach is used, then we would loop over the datatable and construct a list from the column values

Its not about what is recommended, the other person who posted question should get a detailed solution with understanding.

I absolutely agree on this. Regardless what technically is possible a solution approach needs the understanding from the other side.

So we also mentioned the List approach which would look like this:



Lasly the requestor will map his Implementation standards on the different approaches and will find the matching one.

1 Like