Add prefix to whole row

Hi Everyone,
i have one Excel usecase in which i have to add prefix 0000000 to whole row which already contains some data.rows can be more than 4K.
ex:- input:- output:-
column0 column0
2345 00000002345
5435 00000005435


Please use this formula =TEXT(A1,"0000000000")

you can use write cell with "=TEXT(A1,""0000000000"")" in the first cell

then use auto fill range activity


HI @neha.kumarief868

You can try this

  • Read range and save them in the dt variable
  • then build a datatable with only the headers as same as the master data and create another variable
  • Use a Assign
    Build dt variable =below expression
(From d In dt.AsEnumerable
Let f=d(0).ToString
Select dt2.LoadDataRow({f.ToString.PadLeft(11,CChar("0"))},True)).CopyToDataTable

Where dt is the master data , dt2 is build datatable variable

I have created new variable dt called variable1 and used that in assign you can do this or use the same build datatable variable in the assign


1 Like

What is this a1
Already populated data is not same at each row


A1 is the excel cell number…change it to any cel number where your data starts…

And auto fill range activity will take care of changing them sequencially

What the formula does is say in A1 you have 123 and you gave the formula with 00000 5 zeros then 00123 is what gets populated…if next row has 12 then 00012 will be populated, basically 0 are to say how many numbers should be present in the field


Hope this helps


Hi Anil,
its not working

Have you tried with the above expression @neha.kumarief868 ?


If you data is in A2 then use write cell with cell value as B2 not A2 in the formula it would be A2…else it will over write your data

Please check my acreenshot


i have to write on the same cell.

can you help me how to do it in excel without using build datatable

How many columns you will have in the main excel @neha.kumarief868 ?

more than 4K rows
column :-5-7

Can you send a sample excel file with exact headers and dummy data in it @neha.kumarief868 ?

With only 2 or 3 rows

Test.xlsx (60.2 KB)
Material is the column where i have to add 5 zeros

Hi Sudharan,
im not getting the prefix in the excel wheras in the dt & dt2 variable i can see 00000 prefix.

Can you elaborate please ,It is quite not understandable @neha.kumarief868 ?

while debugging i can see 0000prefix in every row of variable dt2 ,but it is not added in the excel.

Give the variable which is in the Build datable to write in the excel and check again @neha.kumarief868

I did the same


You can follow thw above method of excel cormula and then use copy range to copyt the data to required column