Paste value with leading zero

Hi everyone, I am newbie using uipatch and I got a question about pasting value with leading zeros. I have some digits with leading zeros in Excel. Th actual value in excel is ‘0001 so that excel will not convert it into 1. However, when I paste the value by using copy paste range, the leading zeros of the value will be removed when they are pasted into other worksheet. I have a way to solve this by using loop to paste the value by concatenation “‘“ + “00000”.substring(0,5-myvalue.length) + myvalue.tostring. However, the performance is very slow… I tried to read range and keep the format and paste the datatable to another range but no luck.

any smarter solution suggested ?

Thanks everyone!


I assume that you have a definite length for the value, means that the value should be 4 digit or some other length. As you mentioned for ‘0001’ it is to be 4 digit thats why 3 zeros are added to 1.

To add leading zeros you can use this expresion

output = inputString.PadLeft(lenghtExpected,"0"c)

(i.e.) output = (“1”).PadLeft(4,"0"c)

Thanks @Ragu9060 for your prompt reply. The .net expression works perfect. However, using expression to put the value one by one seems it takes time to process given that I have few thousand rows to do that. I am wondering if there is anyway like past range but keeping the original value ‘0001’ as the source when pasting the values in the target range. The situation I encounter now is that when pasting the value contains ‘0001’ and the value become 1 in the target rangeo

Try reading data from excel, manipulate data in datatable it self and write in one shot.


While writting in excel you need to add (’) before your string so that excel will keep your formatting.
For ref Use following
output = “’”+inputString.PadLeft(lenghtExpected,"0"c)

1 Like

Thanks @rahatadi. May I conclude that I still need to “Write Cell” in loop for every single cell and the “Copy and Past Range” activity has no way to keep original value '0001?


I would rather apply formula to entire column.

Fastest way to write an excel formula into an entire column

Refer above thread. :+1:



The fastest and effective way will be using macros,

Sub InsertFormulaToWholeColumn()

Dim rng As range, cell As range
Dim lastRow As Long

lastRow = Worksheets("**Sheet name**").Rows(Worksheets("**Sheet name**").UsedRange.Rows.Count).Row

Set rng = range("B2:B" + CStr(lastRow))

For Each cell In rng

        Worksheets("**Sheet name**").range("**Starting cell index**") = "**Formula**"
Next cell

End Sub

Replace text with with sheet name, formula and range by your needs.

In this macros code notice part where i have used B2:B this is range which will not be empty, for example if you want to fill whole A column with formula then B column will be its reference which will not be empty. Until last row where B column is not empty this macros will enter formula.

Insert this into excel file and use execute macros to achieve your goal. I am sure that this will faster than for each write cell.


@Ragu9060. Thanks so much for your recommendation. I will give a try. My intention is to keep everything in UiPath. I am trying to use HotKey to do Copy and Paste task and the result looks good to me. However, I found that I have to add Delay in between each hotkeys.

Thanks @rahatadi . I am trying to use HotKey to do the task. Hope this works.