Fixed Length, Adding Zeros for an Outline

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
1.1.101.01.01
1.1.201.01.02
1.10.101.10.01
1.10.201.10.02
1.3.101.03.01
10.1.210.01.02
1.1.1001.01.10
10.10.1010.10.10
09.08.0709.08.07
3.8.8503.08.85
8.6.8608.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!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
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...
 
Upvote 0
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"),"")
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top