Fixed Length, Adding Zeros for an Outline

[SirBarnabus]

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
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]1.1.1[/TD]
[TD="class: xl97, width: 64"]01.01.01[/TD]
[/TR]
[TR]
[TD]1.1.2[/TD]
[TD="class: xl97"]01.01.02[/TD]
[/TR]
[TR]
[TD]1.10.1[/TD]
[TD="class: xl97"]01.10.01[/TD]
[/TR]
[TR]
[TD]1.10.2[/TD]
[TD="class: xl97"]01.10.02[/TD]
[/TR]
[TR]
[TD="class: xl95"]1.3.1[/TD]
[TD="class: xl97"]01.03.01[/TD]
[/TR]
[TR]
[TD]10.1.2[/TD]
[TD="class: xl97"]10.01.02[/TD]
[/TR]
[TR]
[TD="class: xl96"]1.1.10[/TD]
[TD="class: xl97"]01.01.10[/TD]
[/TR]
[TR]
[TD="class: xl96"]10.10.10[/TD]
[TD="class: xl97"]10.10.10[/TD]
[/TR]
[TR]
[TD="class: xl96"]09.08.07[/TD]
[TD="class: xl97"]09.08.07[/TD]
[/TR]
[TR]
[TD="class: xl96"]3.8.85[/TD]
[TD="class: xl97"]03.08.85[/TD]
[/TR]
[TR]
[TD="class: xl96"]8.6.86[/TD]
[TD="class: xl97"]08.06.86[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[ChrisR]

Hi,

Can you not do a ctrl + find then do

find . (i.e decimal)
& replace with .0

that would give you 1.01.01.01.01

Then you just need the formula

=0&LEFT(D4,LEN(D4))

to add the leading remaining zero

just change d4 to the relevant cell

hth

chris

 

[SirBarnabus]

Hi ChrisR,

In a word, no, I can't.

This would potentially replace my outline numbering such as 1.10.10 with 1.00.00

Keep in mind that this is 2000-3000 rows...

 

[West Man]

Pretty messy, but it appears to work (limited testing)

=TEXT(LEFT(A1,SEARCH("^",SUBSTITUTE(A1,".","^",1))-1),"00")&
IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))>0,"."&TEXT(MID(A1,SEARCH("^",SUBSTITUTE(A1&".",".","^",1))+1,SEARCH("^",SUBSTITUTE(A1&".",".","^",2))-SEARCH("^",SUBSTITUTE(A1&".",".","^",1))),"00"),"")&
IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))>1,"."&TEXT(MID(A1,SEARCH("^",SUBSTITUTE(A1&".",".","^",2))+1,SEARCH("^",SUBSTITUTE(A1&".",".","^",3))-SEARCH("^",SUBSTITUTE(A1&".",".","^",2))),"00"),"")&
IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))>2,"."&TEXT(MID(A1,SEARCH("^",SUBSTITUTE(A1&".",".","^",3))+1,SEARCH("^",SUBSTITUTE(A1&".",".","^",4))-SEARCH("^",SUBSTITUTE(A1&".",".","^",3))),"00"),"")&
IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))>3,"."&TEXT(MID(A1,SEARCH("^",SUBSTITUTE(A1&".",".","^",4))+1,SEARCH("^",SUBSTITUTE(A1&".",".","^",5))-SEARCH("^",SUBSTITUTE(A1&".",".","^",4))),"00"),"")&
IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))>4,"."&TEXT(MID(A1,SEARCH("^",SUBSTITUTE(A1,".","^",5))+1,99),"00"),"")

 

[West Man]

Further testing seems to confirm. Has itworked for you?

 

[Rick Rothstein]

How about a UDF (user defined function)?

Function Outliner(S As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(S, ".")
  For X = 0 To UBound(Parts)
    Parts(X) = Format(Parts(X), "00")
  Next
  Outliner = Join(Parts, ".")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example,

=Outliner(A2)