excel - Select Text in between 2 given lengths

Question

I'm attempting to extract numerical data from generated lists that lie between 2 given lengths however currently within my code I only Select text with one parameter i.e. less than or equal to 3 charecters in length using Text.Length(_)<=3))

Please note that the data is overly simplified for the sake of the question and there is a reason I wish to seperate out the data using this method.

M Code:

let
Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn( #"Changed Type", "Custom", each Text.Split([Column1], " ")),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column1"}),


//Extract Numerical Value Only into List 
#"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Custom.1", each List.Transform([Custom], each Text.Select(_, {"0".."9", "."}))),

//Extract Values <=3 into List
#"Added Custom2"= Table.AddColumn(#"Added Custom1", "Custom.2", each List.Select([Custom.1],each Text.Length(_)<=3)),

//Combine Extracted Values into New List
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Text.Combine([Custom.2], " ,")),

#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Custom", "Custom.1", "Custom.2"})

in #"Removed Columns"

This code works by generating a list and then selecting only numerical data. Following this only values less than or equal to 3 are extracted into another list and then finally these values are combined.

The issue I have as seen with the current output is when Selecting text <=3 in length Empty cells are also selected.

Is there Any way to Select text such that the length x is: 1<x<=3?

Input Data: 
FF3136451  200
3152678  100
(NDM) 8315865  100
0000660  50

标签： excelpowerquerym

Answer 1

只需将其作为条件添加到您的List.Select

#"Added Custom2"=
    Table.AddColumn(
        #"Added Custom1",
        "Custom.2",
        each List.Select(
                 [Custom.1],
                 each Text.Length(_) <= 3 and Text.Length(_) > 1
             )
),