Split input range in separate values

[Hans_]

Hi all,

I have a user who wishes to have an input cell and a macro will automatically filter input data based on the value in the cell.
The input data that he would like to filter on is numerical but his input will be a text string.

For example, user inputs: "1-3, 6" in the input cell. My data should be filtered on when the column contains numbers 1, 2, 3 and 6.
For example, user inputs: "1, 2,5-7,10 in the input cell. Data should be filtered on numbers 1, 2, 5, 6, 7 and 10.

So, whenever 2 numbers have a "-" in between it will be the numbers containing and in between the 2 numbers.
Each of the values/ranges will be split by a ",". The only values allowed are whole numerical numbers, "-" and ",". Spaces are also allowed by the way but not required. If there is anything else it should throw an error message.

For now i would like to have the result (all separate numbers) in separate cells on a different sheet.
I will then build on from there... (actual filtering and the rest...)

Any ideas?

Thanks!
Hans

 

[sandy666]

using PowerQuery (Get&Tranform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]src[/td][td][/td][td=bgcolor:#70AD47]src.1[/td][td=bgcolor:#70AD47]src.2[/td][td=bgcolor:#70AD47]src.3[/td][td=bgcolor:#70AD47]src.4[/td][td=bgcolor:#70AD47]src.5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1-3, 6[/td][td][/td][td=bgcolor:#E2EFDA]1[/td][td=bgcolor:#E2EFDA]3[/td][td=bgcolor:#E2EFDA] 6[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]1, 2,5-7,10[/td][td][/td][td]1[/td][td] 2[/td][td]5[/td][td]7[/td][td]10[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],
    Replace = Table.ReplaceValue(Source,"-",",",Replacer.ReplaceText,{"src"}),
    Split = Table.SplitColumn(Replace, "src", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"src.1", "src.2", "src.3", "src.4", "src.5"})
in
    Split[/SIZE]

 

[Hans_]

using PowerQuery (Get&Tranform)

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src.1[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src.2[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src.3[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src.4[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src.5[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]1-3, 6[/TD]
[TD][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "] 6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]1, 2,5-7,10[/TD]
[TD][/TD]
[TD]1[/TD]
[TD] 2[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],
    Replace = Table.ReplaceValue(Source,"-",",",Replacer.ReplaceText,{"src"}),
    Split = Table.SplitColumn(Replace, "src", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"src.1", "src.2", "src.3", "src.4", "src.5"})
in
    Split[/SIZE]

Hi Sandy,

Thanks for your reply!

Your answer doesn't give me the correct result. Everything where we have a "-" we would also need all numbers in between.
So, the result of the first one in your example should give 1, 2, 3 and 6.

Regards,
Hans

 

[MickG]

Data on active sheet "A1", Results start sheet3 "A1"

[COLOR="Navy"]Sub[/COLOR] MG15Apr29
[COLOR="Navy"]Dim[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, Sp1 [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] vStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Sp1 = Split([a1], ",")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp1)
    [COLOR="Navy"]If[/COLOR] InStr(Sp1(n), "-") > 0 [COLOR="Navy"]Then[/COLOR]
        Sp = Split(Sp1(n), "-")
        vStr = Join(Application.Transpose(Evaluate("=Row(" & Sp(0) & ":" & Sp(1) & ")")), ",")
        nStr = nStr & IIf(nStr = "", vStr, "," & vStr)
    [COLOR="Navy"]Else[/COLOR]
        nStr = nStr & IIf(nStr = "", Sp1(n), "," & Sp1(n))
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
Sp = Split(nStr, ",")
Sheets("Sheet3").Range("A1").Resize(, UBound(Sp) + 1).Value = Sp

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick