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!
 

ChrisR

Well-known Member
Joined
Dec 21, 2012
Messages
848
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

New Member
Joined
Jul 16, 2013
Messages
5
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
Joined
Mar 27, 2006
Messages
1,175
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"),"")
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,496
Office Version
2010
Platform
Windows
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)
 

Forum statistics

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

Some videos you may like

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...
Top