Accepted Solutions
Community Manager
Byron's suggestion will work perfectly so long as you don't have any characters stored in any of the rows. If you do, those entries will be deleted upon changing the column to numeric.
Since you have those leading zeros in your data that column must already be set as character, I'm assuming because you are storing letters as well as numbers. To eliminate those zeros in place you could use regular expressions in Edit > Search > Find. Regular expression are quite powerful but aren't so friendly to look at. For your situation, the following regular expression will search out leading zeros but will not find zeros that are inside the string:
(?<!\d)0+(?=\d+)
To use this, paste the regular expression in the "Find" section, leave the replace section blank, and check the box for "use regular expressions." I would also check the box for "restrict to selected columns (and make sure you have selected your column of interest). With everything set up, you can now click "replace all" and JMP will find those leading zeros and replace them with blanks. I've also attached a screenshot of this setup below.
I hope this helps!
Julian
Community Manager
Byron's suggestion will work perfectly so long as you don't have any characters stored in any of the rows. If you do, those entries will be deleted upon changing the column to numeric.
Since you have those leading zeros in your data that column must already be set as character, I'm assuming because you are storing letters as well as numbers. To eliminate those zeros in place you could use regular expressions in Edit > Search > Find. Regular expression are quite powerful but aren't so friendly to look at. For your situation, the following regular expression will search out leading zeros but will not find zeros that are inside the string:
(?<!\d)0+(?=\d+)
To use this, paste the regular expression in the "Find" section, leave the replace section blank, and check the box for "use regular expressions." I would also check the box for "restrict to selected columns (and make sure you have selected your column of interest). With everything set up, you can now click "replace all" and JMP will find those leading zeros and replace them with blanks. I've also attached a screenshot of this setup below.
I hope this helps!
Julian
Community Manager
Hi Jarowe,
If your column is a character column, JMP will sort the data as you said, 1, 11, 12, 13, 2, 3 etc. If the column is numeric, JMP will sort the column with natural ordering, 1, 2, 3, 4 etc. Does your column contain characters? If so, changing to numeric won't be an option, so we'll need to do something more creative. What I would do is prepend a number of zeros to the data, and then trim the result to constrain the number of total characters. For instance, if you have the following values, 1a, 2e, 13h, 33f, 201v, what we want is to prepend zeros such that we end up with 001a, 002e, 013h, 033f, 201v. Notice this means that we are prepending a different number of zeros (sometimes 2, sometimes 1, and sometimes none). We could handle this through some conditional logic, but it will be easier to prepend the maximum number of zeros we will need (2) to all the data, and then use a character function called "right" to take just the right most characters from each row. For example, imagine we prepend 2 zeros to every value (which yields 001a, 002e, 0013h, 0033f, 00201v) and then we apply another formula that takes only the right most 4 characters from each (which yields what we want, 001a, 002e, 013h, 033f, 201v). We can do this all through formulas in JMP with just the mouse (and a few key presses).
1. Make a new column by going to Cols menu > New Column.
2. For the data type, select "Character." This is necessary if you wish to have leading zeros and characters (numeric columns will ignore leading zeros as they have no meaning for actual numbers, and can't store letters). Once you do this, click OK
3. Select your new column, then go to Cols menu > Formula
4. This is the formula editor. Here, you can define a formula that will apply to the entire column. What we want is a formula that will prepend a zero (or any characters you want) to your existing values in the original column. We can accomplish this with a character function called "concatenate." Here are the steps to set up that formula:
a. In the upper right section (the function group list) select "Character" and then select "Concat"
b. In the formula editor, you will now see the following:
c. The two rectangles are called arguments of this function-- they need to be populated with either columns or text. In your case, you wish to add values to the start of another column, so first select the second box by clicking on it, and then select the original column in the columns list on the top left. Here is what you should have (but with your own column's name):
d. In my case, the Random ID column is already set as a character column because it has letters. If your original column is numeric, you will need tell JMP to convert the numeric data into a string that you are prepending. To do this, select the "Character" function group again, and select "Char." This will update the formula and add in the instructions to JMP to convert the content of the column you selected to be a character.
e. Next we need to decide what we are prepending. Double click the first rectangle, which will open the editor for that field. Here we want to prepend the MOST number of zeros we will need. For the example I used above, we would prepend with two zeros. Enter (with the quotes) "00" and then hit the enter or return key. Here is what that would look like before we click enter:
f. Once you click return JMP will close the editor for that field. Now, if we were to apply this formula we would end up with two zeros in front of every value. But, the values like "201v" don't need zeros, and if we prepend zeros to those values our sorting won't work. So, we're going to apply one more function to this formula to keep only the rightmost 4 characters. To do this, select the entire formula by clicking the outermost box of the formula we have written. When you do this the box will be highlighted like below:
g. We will now apply one more character function from the character function group. Select the character group, and select "right" This will apply the right() function to this entire formula, and will give us a space to enter the number of characters we wish to extract from the right. In this case, I will enter 4 by double clicking the "n" field and entering 4 (without any quotes). When I click return, this is what I have:
h. Your formula is ready to be run. Click the OK button, and you should see your new column fill with all your original values but prepended with as many zeros as are necessary. In your case you will probably need to modify the number of zeros you add, and the right trim number. To determine this, figure out the number of characters for your highest value (that will be the right trim number), and then determine how many zeros are necessary to add for your smallest value (that will be the number of zeros you should prepend to everything).
I hope this helps!
Julian