SirBarnabus
New Member
- Joined
- Jul 16, 2013
- Messages
- 5
Dear All,
I am trying to create a formula whereby a number is adjusted to a fixed amount of characters using leading zeros. Wait - it doesn't stop there..
The problem I am encountering is that these numbers are in an outline style - 1.1, 1.1.1, 1.1.1.1 and so on.
I need a formula which recognizes the outline level (1, 2, 3 decimals and so on), and fills these with leading numbers.
It's a little complicated, for me at least, so let me give an example:
Original Adjusted
<tbody>
</tbody>
I have a formula for this which works for the above, with 2 decimals:
=CONCATENATE(IF(SEARCH(".",B9,1)=2,TEXT(LEFT(B9,1),"00"),LEFT(B9,2)),".",IF(SEARCH(".",B9,4)-SEARCH(".",B9)=2,TEXT(RIGHT(LEFT(B9,SEARCH(".",B9)+1),1),"00"),RIGHT(LEFT(B9,SEARCH(".",B9,4)-1),2)),".",IF(LEN(B9)-SEARCH(".",B9,4)=1,TEXT(RIGHT(B9,1),"00"),RIGHT(B9,2)))
However, now we have other levels in the outline, so there are 1 (1.1), 2 (1.1.1) and 3 decimals (ie. 1.1.1.1); soon we will have more detail, with 4 (1.1.1.1.1) and up to 5 decimals (1.1.1.1.1.1)... As these varying levels are all in the same column, I'll need just 1 formula which will figure out the number of decimals, and add up to 1 (leading) zero to each section as necessary. This way I can apply it to all outline levels (whether 1, 2, 3, 4, 5 decimals), without having to adjust the formula each time we get deeper into the outline.
Any support you can give me would be fantastic!
I am trying to create a formula whereby a number is adjusted to a fixed amount of characters using leading zeros. Wait - it doesn't stop there..
The problem I am encountering is that these numbers are in an outline style - 1.1, 1.1.1, 1.1.1.1 and so on.
I need a formula which recognizes the outline level (1, 2, 3 decimals and so on), and fills these with leading numbers.
It's a little complicated, for me at least, so let me give an example:
Original Adjusted
1.1.1 | 01.01.01 |
1.1.2 | 01.01.02 |
1.10.1 | 01.10.01 |
1.10.2 | 01.10.02 |
1.3.1 | 01.03.01 |
10.1.2 | 10.01.02 |
1.1.10 | 01.01.10 |
10.10.10 | 10.10.10 |
09.08.07 | 09.08.07 |
3.8.85 | 03.08.85 |
8.6.86 | 08.06.86 |
<tbody>
</tbody>
I have a formula for this which works for the above, with 2 decimals:
=CONCATENATE(IF(SEARCH(".",B9,1)=2,TEXT(LEFT(B9,1),"00"),LEFT(B9,2)),".",IF(SEARCH(".",B9,4)-SEARCH(".",B9)=2,TEXT(RIGHT(LEFT(B9,SEARCH(".",B9)+1),1),"00"),RIGHT(LEFT(B9,SEARCH(".",B9,4)-1),2)),".",IF(LEN(B9)-SEARCH(".",B9,4)=1,TEXT(RIGHT(B9,1),"00"),RIGHT(B9,2)))
However, now we have other levels in the outline, so there are 1 (1.1), 2 (1.1.1) and 3 decimals (ie. 1.1.1.1); soon we will have more detail, with 4 (1.1.1.1.1) and up to 5 decimals (1.1.1.1.1.1)... As these varying levels are all in the same column, I'll need just 1 formula which will figure out the number of decimals, and add up to 1 (leading) zero to each section as necessary. This way I can apply it to all outline levels (whether 1, 2, 3, 4, 5 decimals), without having to adjust the formula each time we get deeper into the outline.
Any support you can give me would be fantastic!