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

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?

Workflow

Output

Custom Tools

Help

Text Mining

Accepted answers

philipmannering

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.

All comments

philipmannering

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!

Alt Ex.pdf

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703