UDF to reformat hierarchical numbering for sorting

T

tjdickinson

Board Regular
Joined
Jun 26, 2021
Messages
61
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'.

Book3.xlsx
ABCDEFG
1NumberingLevel 1Level 2Level 3Level 4JoinedTrimmed
21010  01.001
31.101010 01.01.001.01
41.1.001010001.01.0.001.01.0
51.1.1010101001.01.01.001.01.01
61.201020 01.02.001.02
71.1001100 01.10.001.10
81.10.001100001.10.0.001.10.0
91.10.1011001001.10.01.001.10.01
101.10.10011010001.10.10.001.10.10
112.1.1020101002.01.01.002.01.01
1210.1.1100101010.01.01.010.01.01
Sheet1
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
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
 
Upvote 0
lrobbo314 said:
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
Click to expand...
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
lrobbo314 said:
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
Click to expand...
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
Click to expand...
the following result
01.01.02
01.10.02
Click to expand...
produces unneeded leading zeroes because all substrings have length 2, but
1.01.2
1.10.2
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
Remove text from string between parenthesis leaving only numbers
2
Replies
16
Views
2K
Peter_SSs
Peter_SSs
S
  • Question Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
579
myall_blues
myall_blues
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
124
flashgordie
F
D
  • Question Question
VBA code works when called directly, but not from another routhine
Replies
7
Views
964
davidepstein22
D
etaf
  • Solved
SORT - an Extract List based on criteria - BUT 2019 Version ONLY - NOT 365
Replies
9
Views
810
Georgiboy
Georgiboy

Forum statistics

Threads
1,224,813
Messages
6,181,117
Members
453,021
Latest member
Justyna P

Share this page

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