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