Add " ' " in front of 0 for a valid identifier

[cmStuhl]

Hello, 

I am having an issue where my output into excel is cutting off characters in certain values. For example, an ID that should be 053673 will output 53673. Usually adding an apostrophe at the beginning of the value in excel (such as '053673) fixes this issue.

Is there any way I can add one at the beginning of values in my workflow? 

[PhilipMannering · Accepted solution]

The problem is that you can only add the apostrophe after it's been read in and the damage is already done (the leading zero gets chopped off). Are all the IDs the same length? If so, you could convert to string and padleft.

[cmStuhl]

Okay that makes sense. Ideally I would like all IDs in the column to be either 7 or 9 characters long. I have an if function that then identifies them by their length as either a cusip or sedol. Looking at the excel output it seems there are IDs that are both 6 and 8 characters long. 

Would it be possible to sort the securities by length, then padleft the IDs that are short one digit with a zero to get them to the correct length? 

I have attached my current workflow if that helps provide some more detail. 

Thanks!

[PhilipMannering]

Not sure I follow completely. Maybe you could share a sample of your data. 

But you should be able to do,

padleft([ID], [length of ID], '0')

Where [length of ID] is,

if length([ID]) = 6 then 7 else 9 endif

I'm guessing that last bit.....