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)

src		src.1	src.2	src.3	src.4	src.5
1-3, 6		1	3	6
1, 2,5-7,10		1	2	5	7	10

[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)

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src[/COLOR]		[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src.1[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src.2[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src.3[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src.4[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]src.5[/COLOR]
1-3, 6		1	3	6
1, 2,5-7,10		1	2	5	7	10

<tbody>
</tbody>

[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