split column

[Carlla_n83]

Hello all, 

I need help splitting a column into text and quantity. I've tried using "Split by" and "Text.Remove," but I'm not getting satisfactory results. Is there a quick way to accomplish this? Any suggestions?

[dufoq3 · Accepted solution]

Hi @Carlla_n83, with this example you can do this:

Result

[Anonymous · Accepted solution]

Hi,

Thanks for the solution @dufoq3  and @HotChilli  provided, and i want to offer some more information for user to refer to.

hello @Carlla_n83 , you can create a blank query and put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5LDsIwDESvMuoSIcRPhROwZo+6CKlpoxY7io3o8UnVwIKlR2/e+Hardri8lPAMqoG7qlnPEe4yQR7wPfkBY1BDR2YZwNOlgdrNNQlYNqf6vK+P51ILrJTsz5U11hO8MJM3SQgKE4wiedZxO98sueX8MA8UMAgXwYQ4viLewfrSUksuxt/EdgXtXYrLtb9+H4DRZEt4gIaWssgx6aLS3AB1pAtRF0kBMzdWTfMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type1",{{"Column1.1",each Text.Remove(_,{"x","X","P","p","*"})},{"Column1.2",each  Text.Combine(List.RemoveItems(Text.Split(_," "),{"x","X","P","p","","*"})," "),type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(Custom1,{{"Column1.1", Int64.Type}})
in
    #"Changed Type2"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dufoq3 · Accepted solution]

Hi @Carlla_n83, with this example you can do this:

Result

[HotChilli]

It looks like you can split by space character, once, as far left as possible.  Then use

Text.Select([ColumnName], {"0".."9"})

 to get the number.

I think you have some additional conditions but that will be a good start

[Carlla_n83]

Thank you for your response. so I need to split a column into two separate columns: one for quantity (QTY) and another for reject reasons. The data comes from the production line, and operators use different notations to describe the quantity, such as 15x, 15 x, 15*, 15 *, 15p, 15 p, or 15 sharp edges. Wherever a number is not provided, it should be treated as 1. Any suggestions on how to achieve this? example from excel doc below. thank you 🙂

[HotChilli]

Please define/show what you want.

There are lots of ways to separate numbers and text but only you know what you need from the picture shown.