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

cheers

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

Regards

Sudharsan

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

Cheers

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

Cheers

i have to write on the same cell.

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

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

Hi Sudharan,

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

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

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

Cheers