From RPM Wiki
Excel strips leading zeros
The Excel application automatically removes leading zeros from any number entered into a cell.
From the point of view of spreadsheet functionality this is great unless you need to import a customer account or rep ID that happens to consist entirely of digits and where the first digit is a 0. In this case, part of your account number will be lost before it is ever gets into RPM. Practical implications of this could include unexpected "new accounts" showing up after a commission import or Process Management form updates not finding form 0001 even though you entered 0001 into the excel file.
Placing a single apostrophe (') character ahead of the other characters in a cell will stop excel from automatically altering the cell contents. So if you type '0001 into an excel cell, you will see that the leading zeros are not automatically removed when you hit the enter key.
When RPM imports an excel document, any single leading apostrophe is stripped from the cell value. If you were to import a file containing a cell with the value '0001, RPM would "see" 0001. This means that if you wish to, say, import a form with a number of 0001, you could accomplish this by putting the value '0001 into the number column for the row representing the form (see form import spec).
In order to preserve leading zeros when exporting data, RPM takes the step of pre-pending certain cell values with single apostrophe characters. The rule is that if the cell contains data and the first character is 0, then RPM prepends the cell contents with a single apostrophe.
In this way, RPM is designed to support convenient "round tripping" of data. As an example, if you wanted to update many of the fields in many of the forms in a process, you might want to export a view of that process and enter your updated field values in the cells of the excel file. Then you can use the form importer to update the forms en-masse. If your forms have numbers like 0001, you don’t have to worry about the leading zeros being stripped out because RPM will append an apostrophe when it exports the form view.
Stripping leading apostrophes
RPM automatically strips all leading apostrophe characters from every cell in every Excel import document.
- This page was last modified 21:22, 6 May 2005.
- This page has been accessed 8996 times.