How to keep text format in read range activity

Make sure the ‘PreserveFormat’ property is clicked in your read range activity. This will preserve it so the text value “0123321” will remain text, instead of converting to general, which is then converted to a number in excel (thus removing the leading 0’s)

I try that before but it doesn’t work, seems it only keep currency format but not text format

One thing I’ve noticed is that Excel write range and workbook write range handle the use of Excel formats differently. One keeps it text form (I think workbook write range) while the other pastes it in the general format. Try using whichever one your worklow is not using to see if that fixes it

Thx mate and I think it should be workbook write range coz I am using excel one. I will try it later

Sorry mate it doesn’t work

Hi it works after I add cchar in it.
However I would like to add 2 char because in the excel it won’t display 0 so I have to add ‘ before the integer to change it to text format. However I can’t add ‘0 for following
Cell = cell.tostring.padleft(7, Cchar(“‘0”))
The result becomes ‘’12345.

Thanks.

1 Like

@raymondhui
great…

Sorry mate would u please help
@rahatadi

hi @raymondhui

just add ’ in front of string
'01234
regards,
Aditya

But it won’t show ‘01234
It shows up ‘’1234 which is missing 0

@raymondhui

Yes that’s what I want man would u like to show me your assign activity?
Do u need to add 0 before following?
Cell = cell.tostring.padleft(7,cchar(“‘“))?

Hi. You did that wrong. PadLeft will fill in all characters up to 7 characters, so it is putting single quote to make it ''28784 instead of '0028784

You want to zero fill it with the “0” character, then just place single quote at the start.

Cell = "'"+cell.tostring.padleft(7,cchar(“0“))
3 Likes

Also, my 2 cents on this… if you are dealing with CSV files, they are text-based, so they have all their values already in the zero-filled format that you are looking for. However, if you open the CSV file in Excel, Excel will by Default change the format to a number rather than text.

So, you can use Write CSV instead of Write Range, if that’s an approach you would like to take.
(open the CSV file in Notepad, and you will see how the format is correct)
Note: if you do Write Range to Excel and lose format, you can have UiPath format the columns, then use SaveAs to save to CSV and it will keep the zero-filling when it creates the CSV file.

I normally try to avoid using workarounds like adding the single quote, which also require you to change every value that you have in your table. The other option is to format the Excel file with 0000000 formatting for a visual but the value won’t have the zeros, so that option may not be what you want either.

So, I guess it depends on what the end goal really is, and using CSVs are usually better for keeping zero-filled numbers and date formats intact.

Regards.

3 Likes

@raymondhuiMain.xaml (7.8 KB)
Book1.xlsx (8.6 KB)

refer this…!

thanks @ClaytonM

this would be great practice to use csv.

Thx for your advice and works finally it works fine!! Yes coz I have other works on the worksheet therefore I choose to use write range. Btw such a simple work but I miss the simple steps, thank you very much!

Thx for help man yes it works!

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.