Custom Data Type to String Conversion

Hi All,

I have a column in excel which has 14 digit number with 0 prefix. So, I changed the whole column data format to customer and enter 14 zeros to populate the correct no. I want this column to be converted as text. Pl see the screenshot:


I want to convert like this:


Moreover, when I copy it in another sheet so it shows the General Data Type and look like this:


Many thanks.

Hi @Dr_Raza_Abidi_PhD

if your organization allows to use third party package means
Install Balareva.Excel.Activities

after that in the activities panel search for change cell type activity

if you want to convert whole column like A means you should declare like this “A:A” in cell Tab
copy the destination cell format in excel
follow these steps to find cell format Right click the destination formatted column → Format cells → custom ->copy the value from the type tab

paste the copied format into cell format tab in change cell type activity

finally choose excel file path in change cell type activity

thats it

hope its solves your issue



Thanks @Robinnavinraj_S, Our organization does not allow us to use third party package. Could you please share another solution instead.

Many thanks,

hi @Dr_Raza_Abidi_PhD

Try this expression


@Sudharsan_Ka : My data not always start with 0, it can also start with other than zero (i.e., 1,2,3,4, and 5). So, your code will be good where we want prefix as zero, and I think through IF condition using Length function we can check. Pl. guide.


Hi @Dr_Raza_Abidi_PhD

we can use power shell script also

$ExcelObject = new-Object -comobject Excel.Application
$strPath1=“D:\Powershell\New Microsoft Excel Worksheet.xlsx”
$ActiveWorkbook = $ExcelObject.WorkBooks.Open($strPath1)
$ActiveWorksheet = $ActiveWorkbook.Worksheets.Item(1)
$ActiveWorksheet.columns.item(‘A’).NumberFormat = “0”

Save the above script into text file

Replace the highlighted text below as per your document in the script
provide your excel file path in $strPath1 ="D:\Powershell\New Microsoft Excel Worksheet.xlsx"
provide your destination sheet index as 1,2… $ActiveWorkbook.Worksheets.Item(1)
Provide your column index want to change like A,B,C… $ActiveWorksheet.columns.item(‘A’).NumberFormat = “0”

read that saved text file using read text file activity

pass the output variable of read text file activity into invoke power shell activity and enable isscript checkbox

thats it

Hope its solves your issue


1 Like

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