Copy Column and Retain Format

Help Activities
, ,
1

Hi,

I have a problem in retaining cell format in excel. I have to copy this one column and append to the last row of another excel file. I have done this part but the only problem is the format is gone so the value that being append is invalid.

Example:

Excel 1:
image

Excel 2:
image

So I have to append data from column ‘ID_NO’ in excel 2 to column ‘RID’ in excel 1 without removing the format.

Currently what I did is I use filter datatable activity and get column ‘ID_NO’ and append it using write range activity.

The problem is after append all the values that starts with 0 will be gone in Excel 1. Example as below:
image

Note: Both excel I have changed it to Text format.

2

Hi @jenkim ,
Your problem can be solved here:-

Read the steps in : How to show leading zeroes in Excel using a custom number format

3

From what I understand from this is basically we add the zero manually. But that’s not the case in my issue.

The number of leading zeros are random. It may have one leading zero or may have 2 or 3. I also have change it to text format but after append it the leading zeros are gone.

4

Hi @jenkim,

Follow the below steps

  • When you read the excel 1 in read range property just check on the preserve format option
    image
    image1121×150 7.04 KB
  • In the excel 2 make the column format as text and then try to append the values.
5

I have try this before and it doesn’t work. After read range and use filter datatable to filter that column and write range, the leading zeros are gone.