# Fixed Length, Adding Zeros for an Outline

#### SirBarnabus

##### New Member
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:

 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!

#### ChrisR

##### Well-known Member
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))

just change d4 to the relevant cell

hth

chris

#### SirBarnabus

##### New Member
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

##### Well-known Member
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

##### Well-known Member
Further testing seems to confirm. Has itworked for you?

#### Rick Rothstein

##### MrExcel MVP
How about a UDF (user defined function)?

Code:
``````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)

1,081,423
Messages
5,358,595
Members
400,505
Latest member
JacquiT

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...