tjdickinson

New Member
Joined
Jun 26, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
The fact that Excel is unable to identify and correctly sort hierarchical numbering (ex. 1.1, 1.2, 1.2.1, 1.2.2, 1.3...) is an age-old problem which has been widely discussed in many fora. There are numerous workarounds, but they are often project-specific or formula-based. At the moment, I have a long, ugly, suboptimal, and restrictive formula, but I would like to have a UDF which accomplishes the same thing more efficiently, and which I can call whenever I need it (rather than having to reuse and adapt the formula each time).

The formula I have is based on a workaround posted here by Steve K, and demonstrated in the YouTube link posted by Nehemiah. It takes a number (ex. 1.1.1) and converts it into the format 00.##.##.## (thus, ex. 01.01.01). In the following sheet, the formula is in column G and is independent of all columns except A. Columns B-F contain segments of the formula to visualise how it works: it decomposes the input (column A) into hierarchical levels with the format 00 (columns B-E), and then joins them together with the delimiter "." (column F). In the final formula, then, is also a function to trim the string, removing the final ".0". (I suppose it's not necessary to begin with; it's an artefact of the 'sort by columns B-E' method described in the aforementioned YouTube link. But the formula is so unwieldy that I can't be bothered to take it out.) Note that all cells have the format 'text'.

Cell Formulas
RangeFormula
B2:B12B2=IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,IF(LEN(A2)>1,A2,"0"&A2),IF(LEN(LEFT(A2,SEARCH(".",A2)-1))>1,LEFT(A2,LEN(LEFT(A2,SEARCH(".",A2)-1))),"0"&LEFT(A2,LEN(LEFT(A2,SEARCH(".",A2)-1)))))
C2:C12C2=IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,"0",IFERROR(IF(LEN(MID(A2,SEARCH(".",A2)+1,FIND("~",SUBSTITUTE(A2,".","~",2))-(SEARCH(".",A2)+1)))=1,"0"&MID(A2,SEARCH(".",A2)+1,FIND("~",SUBSTITUTE(A2,".","~",2))-(SEARCH(".",A2)+1)),MID(A2,SEARCH(".",A2)+1,FIND("~",SUBSTITUTE(A2,".","~",2))-(SEARCH(".",A2)+1))),IF(LEN(RIGHT(A2,LEN(A2)-SEARCH(".",A2)))=1,"0"&RIGHT(A2,1),RIGHT(A2,LEN(A2)-SEARCH(".",A2)))))
D2:D12D2=IF(IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,"",IF(IFERROR(FIND("~",SUBSTITUTE(A2,".","~",2)),"")="","0",IF(LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))=1,"0"&RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))),RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))))))="00","0",IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,"",IF(IFERROR(FIND("~",SUBSTITUTE(A2,".","~",2)),"")="","0",IF(LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))=1,"0"&RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))),RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2)))))))
E2:E12E2=IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))<2,"","0")
F2:F12F2=TEXTJOIN(".",TRUE,IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,IF(LEN(A2)>1,A2,"0"&A2),IF(LEN(LEFT(A2,SEARCH(".",A2)-1))>1,LEFT(A2,LEN(LEFT(A2,SEARCH(".",A2)-1))),"0"&LEFT(A2,LEN(LEFT(A2,SEARCH(".",A2)-1))))),IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,"0",IFERROR(IF(LEN(MID(A2,SEARCH(".",A2)+1,FIND("~",SUBSTITUTE(A2,".","~",2))-(SEARCH(".",A2)+1)))=1,"0"&MID(A2,SEARCH(".",A2)+1,FIND("~",SUBSTITUTE(A2,".","~",2))-(SEARCH(".",A2)+1)),MID(A2,SEARCH(".",A2)+1,FIND("~",SUBSTITUTE(A2,".","~",2))-(SEARCH(".",A2)+1))),IF(LEN(RIGHT(A2,LEN(A2)-SEARCH(".",A2)))=1,"0"&RIGHT(A2,1),RIGHT(A2,LEN(A2)-SEARCH(".",A2))))),IF(IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,"",IF(IFERROR(FIND("~",SUBSTITUTE(A2,".","~",2)),"")="","0",IF(LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))=1,"0"&RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))),RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))))))="00","0",IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,"",IF(IFERROR(FIND("~",SUBSTITUTE(A2,".","~",2)),"")="","0",IF(LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))=1,"0"&RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))),RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))))))),IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))<2,"","0"))
G2:G12G2=LEFT(TEXTJOIN(".",TRUE,IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,IF(LEN(A2)>1,A2,"0"&A2),IF(LEN(LEFT(A2,SEARCH(".",A2)-1))>1,LEFT(A2,LEN(LEFT(A2,SEARCH(".",A2)-1))),"0"&LEFT(A2,LEN(LEFT(A2,SEARCH(".",A2)-1))))),IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,"0",IFERROR(IF(LEN(MID(A2,SEARCH(".",A2)+1,FIND("~",SUBSTITUTE(A2,".","~",2))-(SEARCH(".",A2)+1)))=1,"0"&MID(A2,SEARCH(".",A2)+1,FIND("~",SUBSTITUTE(A2,".","~",2))-(SEARCH(".",A2)+1)),MID(A2,SEARCH(".",A2)+1,FIND("~",SUBSTITUTE(A2,".","~",2))-(SEARCH(".",A2)+1))),IF(LEN(RIGHT(A2,LEN(A2)-SEARCH(".",A2)))=1,"0"&RIGHT(A2,1),RIGHT(A2,LEN(A2)-SEARCH(".",A2))))),IF(IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,"",IF(IFERROR(FIND("~",SUBSTITUTE(A2,".","~",2)),"")="","0",IF(LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))=1,"0"&RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))),RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))))))="00","0",IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,"",IF(IFERROR(FIND("~",SUBSTITUTE(A2,".","~",2)),"")="","0",IF(LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))=1,"0"&RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))),RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))))))),IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))<2,"","0")),LEN(TEXTJOIN(".",TRUE,IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,IF(LEN(A2)>1,A2,"0"&A2),IF(LEN(LEFT(A2,SEARCH(".",A2)-1))>1,LEFT(A2,LEN(LEFT(A2,SEARCH(".",A2)-1))),"0"&LEFT(A2,LEN(LEFT(A2,SEARCH(".",A2)-1))))),IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,"0",IFERROR(IF(LEN(MID(A2,SEARCH(".",A2)+1,FIND("~",SUBSTITUTE(A2,".","~",2))-(SEARCH(".",A2)+1)))=1,"0"&MID(A2,SEARCH(".",A2)+1,FIND("~",SUBSTITUTE(A2,".","~",2))-(SEARCH(".",A2)+1)),MID(A2,SEARCH(".",A2)+1,FIND("~",SUBSTITUTE(A2,".","~",2))-(SEARCH(".",A2)+1))),IF(LEN(RIGHT(A2,LEN(A2)-SEARCH(".",A2)))=1,"0"&RIGHT(A2,1),RIGHT(A2,LEN(A2)-SEARCH(".",A2))))),IF(IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,"",IF(IFERROR(FIND("~",SUBSTITUTE(A2,".","~",2)),"")="","0",IF(LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))=1,"0"&RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))),RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))))))="00","0",IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))=0,"",IF(IFERROR(FIND("~",SUBSTITUTE(A2,".","~",2)),"")="","0",IF(LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))=1,"0"&RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))),RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,".","~",2))))))),IF(LEN(A2)-LEN(SUBSTITUTE(A2,".",""))<2,"","0")))-2)

The following table shows the result of A-Z sorting column A (incorrect sequence) and column G (correct sequence).

Sorted-ASorted-G
101
1.101.01
1.1.001.01.0
1.1.101.01.01
1.1001.02
1.10.001.10
1.10.101.10.0
1.10.1001.10.01
1.201.10.10
10.1.102.01.01
2.1.110.01.01

So, the formula works, but there are loads of problems with it:
  • it is restricted to three levels of hierarchy, and two digits per level
  • it is extremely repetitive and thus suboptimal
  • it contains many references to cell A2, which makes it cumbersome to reuse in other sheets (when A2 isn't the desired reference)
  • it is difficult to modify and debug
I think it would be much more efficient and much less restrictive as a UDF, such as HIERARCHY(reference) or HIERARCHY(array). The problem is I'm an absolute novice when it comes to VBA, and it would take me ages to figure it out. I'll have a go, but in the meantime, if a generous user out there wouldn't mind having a go at it, here's what I'm looking for:
  • an efficient function
  • that converts an input from a cell or array
  • from a format '##.##.##'...
    • even better if it can identify any non-numeric (or non-alphanumeric) character as a delimiter; thus it would also process inputs such as: '##/##-##.##|##'
  • to a format '00.00.00'...
    • all delimiters are converted to "."
  • ensuring that each value in a given segment/hierarchy level has the same number of digits
    • In other words: if a hierarchy level contains only single digits, then the format is '0'; if it contains any double digits, then the format is '00'; if it contains any triple digits, then the format is '000', etc. Thus, each segment/hierarchy level can have different numbers of digits, but it needs to be consistent down the column (or the sorting won't work).
  • and that the returned result is displayed as text (not as a number)
Lastly, it would be great if the same function could also process alphanumeric entries. This might be potentially too complicated (or impossible), so I don't consider it essential. But this would mean, for example, being able to sort more outline-style numbering, such as 1.A.1, 1.A.2, 1.B.1, 1.B.2, etc. Additionally, an entry like '1.1 A' should sort directly after '1.1' (but after 1.1.1 etc.). (To put it more concretely: I sometimes have lists including 4.1, 4.2, 4.3, 4.1 BG, 4.2 BG, 4.4 ..., and 4.1 BG should be sorted after 4.1 and before 4.2, and 4.2 BG should be sorted after 4.2 and before 4.3.)

Thank you very much in advance for your help. I really appreciate it!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,254
Office Version
  1. 365
Platform
  1. Windows
How about this?

MXL 20210707.xlsm
ABCDEFG
1NumberingJoinedTrimmedVBA365
21110101
31.101.01.01.0101.0101.01
41.1.001.01.0.001.01.001.0101.01
51.1.101.01.01.001.01.0101.01.0101.01.01
61.201.02.01.0201.0201.02
71.101.01.01.0101.0101.01
81.10.001.10.0.001.10.001.1001.10
91.10.101.10.01.001.10.0101.10.0101.10.01
101.10.1001.10.10.001.10.1001.10.1001.10.10
112.1.102.01.01.002.01.0102.01.0102.01.01
1210.1.110.01.01.010.01.0110.01.0110.01.01
Sheet1
Cell Formulas
RangeFormula
E2:E12E2=fs(A2)
G2:G12G2=LET(s,A2,a,FILTERXML("<x><y>" & SUBSTITUTE(s,".","</y><y>") & "</y></x>","//y"),f,FILTER(a,a>0),TEXTJOIN(".",1,TEXT(f,"00")))


VBA Code:
Function FS(s As String)
Dim SP() As String:     SP = Split(s, ".")

With CreateObject("System.Collections.ArrayList")
    For i = 0 To UBound(SP)
        If SP(i) <> "0" Then
            .Add Format(SP(i), "00")
        End If
    Next i
    FS = Join(.toArray, ".")
End With
End Function
 

tjdickinson

New Member
Joined
Jun 26, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
How about this?

VBA Code:
Function FS(s As String)
Dim SP() As String:     SP = Split(s, ".")

With CreateObject("System.Collections.ArrayList")
    For i = 0 To UBound(SP)
        If SP(i) <> "0" Then
            .Add Format(SP(i), "00")
        End If
    Next i
    FS = Join(.toArray, ".")
End With
End Function
Thanks so much, lrobbo314, this is a great start!

The biggest problem with it is that it doesn't keep intentional trailing zeroes. For example, '1.1.0' results in '01.01'. It definitely shouldn't add trailing zeroes (so '1.1' should, and does, result in '01.01', and not '01.01.00'), but if a trailing zero is included in the input, it should be retained in the output (thus, '1.1.0' should result in '01.01.00').

Another, more minor, thing is that it only takes a single cell input, ex. FS(A2). I would like it to be able to take an array, ex. FS(A2:A12). However, this is only necessary if it were possible, perhaps via a subroutine, to analyse the substrings in the SP() array. I think I didn't explain it well at the start. I'll visualise it in a table:

InputSub-1Sub-2Sub-3ResultFS()
1100101
1.111001.101.01
1.1.1111001.1.0101.01.01
1.212001.201.02
1.2.101210001.2.1001.02.10
2.1.1211002.1.0102.01.01
12.1.11211012.1.0112.01.01
100.3.20100320100.3.20100.03.20
110.1.111011110.1.01110.01.01
Max Len312

The first column is the input. Columns two, three, and four are essentially the values stored in the array SP() (so, SP(0)=Sub-1, SP(1)=Sub-2, SP(2)=Sub-3). What I'd like is for it to then consider all values of the array in each index to determine the max length of that index. Thus, when we look at all the values under Sub-1, the max length is 3; under Sub-2, the max length is 1; and under Sub-3 the max length is 2. These values then determine the number of digits in the formatting of that substring (000, 0, and 00, respectively).

There are two reasons for doing this: first, it reduces clutter by adding preceding zeroes only when needed for the sorting; and second, it ensures the sorting works correctly when a substring has 3 or more digits. If we were to sort the above table by the Input or FS() columns, then '12.1.1' comes after '110.1.1', rather than before '100.3.20'. Thus, it should be rendered as '012.1.01' in order to sort correctly.

Thanks again!
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,254
Office Version
  1. 365
Platform
  1. Windows
How about this? This code assumes that there isn't any data below the table.

Book1 (version 2).xlsb
ABCDE
1InputSub-1Sub-2Sub-3VBA
211001
31.111001.1
41.1.1111001.1.01
51.212001.2
61.2.101210001.2.10
72.1.1211002.1.01
812.1.11211012.1.01
9100.3.20100320100.3.20
10110.1.111011110.1.01
Sheet1


VBA Code:
Sub FS()
Dim r As Range:         Set r = Range("B2:D" & Range("B" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2
Dim cLen As Variant:    ReDim cLen(1 To r.Columns.Count)
Dim res() As Variant:   ReDim res(1 To r.Rows.Count)
Dim cInd As Integer:    cInd = 1
Dim tmp() As Variant
Dim tRes As String
Dim c As Range

For Each c In r.Columns
    cLen(cInd) = Evaluate(Replace("Max(Len(@))", "@", c.Address))
    cInd = cInd + 1
Next c

For i = 1 To UBound(AR)
    tmp = Application.Index(AR, i, 0)
    tRes = "'"
    For j = 1 To UBound(tmp)
        If tRes <> "'" And tmp(j) <> vbNullString Then tRes = tRes & "."
        If tmp(j) <> "'" Then tRes = tRes & Format(tmp(j), Application.WorksheetFunction.Rept("0", cLen(j)))
    Next j
    res(i) = tRes
Next i

r.Resize(, 1).Offset(, 3).Value = Application.Transpose(res)

End Sub
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,254
Office Version
  1. 365
Platform
  1. Windows
Here's a way using Power Query.

Book1 (version 2).xlsb
ABCDEFGHIJ
1InputSub-1Sub-2Sub-3InputSub-1Sub-2Sub-3Custom
2111001001
31.1111.10011001.1
41.1.11111.1.1001101001.1.01
51.2121.20012001.2
61.2.1012101.2.10001210001.2.10
72.1.12112.1.1002101002.1.01
812.1.1121112.1.1012101012.1.01
9100.3.20100320100.3.20100320100.3.20
10110.1.111011110.1.1110101110.1.01
Sheet1


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Types = Table.TransformColumnTypes(Source,{{"Input", type text}, {"Sub-1", Int64.Type}, {"Sub-2", Int64.Type}, {"Sub-3", Int64.Type}}),
    Unpivot = Table.UnpivotOtherColumns(Types, {"Input"}, "Attribute", "Value"),
    GroupedRows = Table.Group(Unpivot, {"Attribute"}, {{"Count", each _, type table [Input=nullable text, Attribute=text, Value=number]}}),
    Max = Table.AddColumn(GroupedRows, "Max", each Text.Length(Text.From(List.Max([Count][Value])))),
    Expand = Table.ExpandTableColumn(Max, "Count", {"Input", "Value"}, {"Input", "Value"}),
    Pad = Table.AddColumn(Expand, "Pad", each Text.PadStart(Text.From([Value]),[Max],"0")),
    RC = Table.RemoveColumns(Pad,{"Value", "Max"}),
    Pivot = Table.Pivot(RC, List.Distinct(RC[Attribute]), "Attribute", "Pad"),
    Combine = Table.AddColumn(Pivot, "Custom", each Text.Combine(List.RemoveNulls(List.Skip(Record.FieldValues(_),1)),".")),
    Sort = Table.Sort(Combine,{{"Custom", Order.Ascending}})
in
    Sort
 

tjdickinson

New Member
Joined
Jun 26, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
How about this? This code assumes that there isn't any data below the table.

Book1 (version 2).xlsb
ABCDE
1InputSub-1Sub-2Sub-3VBA
211001
31.111001.1
41.1.1111001.1.01
51.212001.2
61.2.101210001.2.10
72.1.1211002.1.01
812.1.11211012.1.01
9100.3.20100320100.3.20
10110.1.111011110.1.01
Sheet1


VBA Code:
Sub FS()
Dim r As Range:         Set r = Range("B2:D" & Range("B" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2
Dim cLen As Variant:    ReDim cLen(1 To r.Columns.Count)
Dim res() As Variant:   ReDim res(1 To r.Rows.Count)
Dim cInd As Integer:    cInd = 1
Dim tmp() As Variant
Dim tRes As String
Dim c As Range

For Each c In r.Columns
    cLen(cInd) = Evaluate(Replace("Max(Len(@))", "@", c.Address))
    cInd = cInd + 1
Next c

For i = 1 To UBound(AR)
    tmp = Application.Index(AR, i, 0)
    tRes = "'"
    For j = 1 To UBound(tmp)
        If tRes <> "'" And tmp(j) <> vbNullString Then tRes = tRes & "."
        If tmp(j) <> "'" Then tRes = tRes & Format(tmp(j), Application.WorksheetFunction.Rept("0", cLen(j)))
    Next j
    res(i) = tRes
Next i

r.Resize(, 1).Offset(, 3).Value = Application.Transpose(res)

End Sub
Thanks, lrobbo314, but I'm afraid my attempt at clarity only confused things. Sorry about that. I provided the table only to show what is meant to happen 'behind the scenes', so to speak. This table is normally not part of the documents, only column A is. I found it difficult to explain without the visual reference that all substrings at a given 'level' need to be the same length, but that it isn't necessary for all substrings in all levels to have the same length. Thus, given the inputs:
1.1.2
1.10.2
the following result
01.01.02
01.10.02
produces unneeded leading zeroes because all substrings have length 2, but
1.01.2
1.10.2
is better formatted because the max length of the first substring is 1, of the second is 2, and of the third is 1, so leading zeroes are only used when necessary to ensure correct sorting.

I'll try explaining it another way, in case it helps. This is how I 'imagine' it working, and probably how I (as a novice) would try to go about coding it. I'm sure there's a more efficient way to achieve the same result, though. Anyways, I hope it gets the idea across more clearly.

In the worksheet:
In cells B2:B152, user enters the array function
Excel Formula:
=FS(A2:A152)
Ctrl+Shift+Enter
Note that the ranges B2:B152 and A2:A152 are arbitrary.

Behind the scenes, in the VBA UDF:
1. The function takes the inputs from cells A2:A152, splits them at the delimiter, and stores each substring in a matrix M:
1625816410647.png

Note that each substring should be in its simplest form (ex. format ####, i.e. without any leading zeroes). In other words, if A2 contains '1.01.1', then row 1 of the matrix contains '1, 1, 1' (and not '1, 01, 1').

2. The function finds max(len(Mr,n))=p(n), i.e. the max length p of all rows r in column n of matrix M, and stores them in an array:
length(p(n0), p(n1), p(n2), ... , p(nn))

3. The function evaluates each element p(n) in the array length() and produces a new array fmt() containing strings of zeroes of lengths p, as in the following schema:
if length(n) = (i.e. if p(n) = )then fmt(n) =
10
200
3000
40000

Thus length(2, 3, 1) would produce fmt(00, 000, 0).

4. The function applies format fmt(n) to Mr,n. Each row r in column n in the matrix M is formatted according to fmt(n). Following the previous example, fmt(0)=00, thus column 0 in M receives the format '00'; fmt(1)=000, thus column 1 in M receives the format '000'; etc.

5. The function joins each element in a row with the delimiter "." and stores it in a vertical array result().

6. The function returns the vertical array in cells B2:B152.

Back in the worksheet:
After user types Ctrl+Shift+Enter, the preceding function is carried out in the background, and the values displayed in B2:B152 are the elements of array result().

When FS() is not entered as an array, an error is returned. Otherwise, the result would be identical to the input (since it would evaluate each cell individually with matrix dimension 1 x n), which defeats the purpose.

Potentially, the function could have the following optional arguments:
  • input delimiter: the user defines the delimiter used in the input values (applies to step 1). If the input is '1-21-1', then the user puts "-" in this argument. If this argument is not defined, then the default is "."
  • output delimiter: adding a bit of customisation to the process, this allows the user to specify the delimiter used when the substrings are joined (step 5). If this argument is not defined, then if the 'input delimiter' is defined, it is used, otherwise the default is "."
  • minimum length: this argument allows the user to define the minimum length of substrings in the result. If the input is '2', then 1.1.1 would be returned as '01.01.01'.
    • Including this argument would add a step between 2 and 3:
2.1. The function compares each element p(n) in length() to the user defined value 'minimum length' s. If p(n)<s, then p is redefined so p(n)=s. If p(n)>=s, then p is unchanged.


I hope this helps clear things up. I'm really grateful for the effort you put in, and I'm terribly sorry that my lack of clarity led you off track.
 

Forum statistics

Threads
1,141,571
Messages
5,707,151
Members
421,494
Latest member
Chiggi_897

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
Top