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:

image

I want to convert like this:

image

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

image

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
image

if you want to convert whole column like A means you should declare like this “A:A” in cell Tab
image
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
image

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

finally choose excel file path in change cell type activity

thats it

hope its solves your issue

Thanks,
Robin

3 Likes

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
CDbl(row(“Account”).tostring).ToString("#,##0.00")

Regards
Sudharsan

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

Thanks,

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”
$ActiveWorkbook.Save()
$ActiveWorkbook.close($true)
$ExcelObject.quit()

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

Thanks,
Robin

1 Like

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