Hi, i have set of data as below image:
1) All the data were formatted in Text as i would like to retain the leading zero. The manipulation result also need to retain the leading zero.
2) I would like to manipulate the data digit by digit, instead as a whole number.
3) The digit manipulation result rotates in range of 0 to 9 (0,1,2,3,4,5,6,7,8,9,0,1,2...), which means
3.1) 3 rotate forward 7 times become 0
3.2) 5 rotate forward 7 times become 2
3.3) 0 rotate backward 7 times become 3
For Manipulation 1:
-if the digit is in range of 1 to 5, rotate forward 7 times .
-if the digit is in range of 6 to 9, or 0, rotate backward 7 times.
For example, cell B2 is 3045, then the manipulation result be populated to cell B3 as 0312
For Manipulation 2:
-if the digit is 1,3,5,7, or 9, then rotate forward 7 times .
-if the digit is 2,4,6,8, or 0, then rotate backward 7 times.
For example, cell C2 is 6041, then the manipulation result be populated to cell C3 as 9178
Many thanks in advance