Add Zeros Wizard
This adds zeros or any other characters to the given content of each cell in the active selection or a selection you specify.
You can add data in front, within or at the end of the the cell content. Data to add can be of any type: strings or parts of a formula.
By default, the Wizard will add leading zeros in front of the cell content and create a 6 digit long string. In case the original cell content was shorter than 6 digits, it will be filled (from the left side) with as many zeros as required to create a 6 digit long string. Exactly that had been the original purpose of this tool. Over the time, more functionality was added to it. Now, you can add everything to the given content and even to a function.
In case (leading) Zeros or something else than the default zeros were added, a string as long as specified in Overall Length will be created showing the added information at the position specified (Before, After or in between).
In case the cell content does get longer than specified within Overall Length, the string will be truncated either from the left or the right side, depending on the position specified in Add Where. In case data was added Before the existing cell content, the string will be truncated from the left, otherwise from the right side (After). Placing data in the middle of a given content will always truncate from the left side.
By default, the tool will work with values of a cell or range. Alternatively, you can add content to a function within a given range, adjusting it to calculate something new. This added flexibility makes the tool more complex to handle but it is still easy to use - just read on.
Add What:
First, specify what to add: Zeros or any other characters entered in the textbox. If you selected Special, add your phrase to add - this might be simple strings as MyText, direct calculation strings, i.e. +1+1 or cell references performing calculations, i.e +(A1*7).
If Skip Empty Cells is activated as well, only cells filled with data will be changed.
By default, the tool will add the given characters to the value and not to the function of cells, i.e. when adding XYZ, a cell content like =SUM(A1:B1) would become XYZ2 instead of XYZ=SUM(A1:B1). Choose Add To Function instead, in order to add data to the function of the cell(s).
In case you decide to add zeros to a function, the tool will create a new function which will encapsulate the existing function and add zeros to it.
|
Ex.: |
The existing function =A5+A6 might be converted to =RIGHT("000000"&A5+A6;6) using default settings. You can alternatively specify creating a function which will add zeros at the end of the existing function result. You cannot create a function which will add Zeros to the middle of a function - this does not make sense. |
|
|
|
The tool will not allow this by disabling the appropriate options. Anyhow, Zeros might be added to the right, giving a function like: =LEFT(IF(OR(A3="";A4="");"";A3+A4)&"000000";6). |
The tool will guide you through creating the wanted adjustments. In case you selected Special and Add To Function all options of Overall Length are disabled - the tool selected the best settings for you.
Overall Length:
Set the length of the cell content. By default, the length of the cell content will be set to 6 characters.
When adding to Values the resulting string will have a fixed length as specified (or shorter). When adding to Function the resulting length will be as long as possible (and cannot be changed). The possible length of MS Excel cell content is 32.767 characters.
When adding Zeros to a Function the resulting function will use the specified length to truncate the functions result.
Add Where:
Specify where to add characters. By default the characters will be added at the beginning of the cell content.
When adjusting existing functions you might want to add something into the middle of it. You can use the loupe icons to easily identify the character where to add.
The supporting dialog will read the function of the active cell and display its content in a textbox. Place the cursor in the textbox in order to identify the position of interest. Once you found the position confirm the dialog with OK and the position will be written into the Add Zeros Wizard.
Note: In case the active cell does not contain a function (because it is not the cell you want to work with) proceed to Tab Area to Add and select a cell by help of the RefEdit Control. Afterwards switch back to Add Where and use the dialog to determine the desired position.
Area to Add:
Specify the cell or selection of cells to amend. By default, the active selection of cells will be amended.
Data can be added to selections built of multiple areas as well. You simply have to select the single areas beforehand. See Selection Wizard for details regarding using previously used selections anew.
Note: If a User Defined selection contains multiple areas, only the first area will be filled. All other areas stay unchanged. To use multiple areas, they have to be already selected and Selected Cell(s) has to be chosen.
Note: If adding to Value, the tool will not apply a style, building leading zeros, to the selected cell(s) but assign the cell-format "Text" to the selection and add real zeros. If Skip Empty Cells has been selected as well, all empty cells wthin the selection will get this style as well.
In case the sheet is protected, no styles can be assigned to its cells - the macro will show a warning message and stop. Unprotect the sheet first.
In case adding to Function has been set and the resulting function is erreneous, the tool will stop with a warning message. Afterwards the faulty function result will be shown. You have to create a correct function by your addings in order to amend the existing function successfully.
Run Save to permanently save changed settings. By default, the Add Zeros Wizard will start with its default settings. Once any changes were saved, it will always use the saved settings.
To set the Wizard back to its default settings, run Default and afterwards Save.
For details about saving see Save Changes within XLplus.
For details about using the RefEdit Control see RefEdit Control Element.