Paste value with leading zero

Help
,
1

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!

2

@Benny_Chan

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 vb.net expresion

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

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

3

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

4

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

5

@Benny_Chan

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
6

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?

7

@Benny_Chan

I would rather apply formula to entire column.

Fastest way to write an excel formula into an entire column

Refer above thread. :+1:

2 Likes
8

@Benny_Chan

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.

2 Likes
9

@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.

10

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