#### SirBarnabus

##### New Member

- Joined
- Jul 16, 2013

- Messages
- 5

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!