How to extract specific number of characters before a delimiter occuring several times?

[Betty888]

Dear all,

I have a column that looks like : 

01/ dddddd

02/ aaaaaaa

03/ nnnnnnn

and I need to split into 2 columns in a way : 

- colonne 1 : the 2 characters before the delimiter ( / ) and separate them by "#(lf)"

- colonne 2 : the text between delimiters :  "/" and "#(lf)"

Thanks a lot for your help ! 

[Nathaniel_C]

Hi @Betty888 ,
Please try this - go to Power Query, go to the Transform tab, Split the column using "/" then trim the column to get this.
Here is the code that you can paste into the Advanced Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUV0gBA6VYHSDXSF8hEQIgfGN9hTwIUIqNBQA=", 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.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Column1.2", Text.Trim, type text}})
in
    #"Trimmed Text"

 
Let me know if you have any questions on any of the steps.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Betty888]

Hi @Nathaniel_C ,

many thanks for your reply 🙏

This works perfectly when I have the delimiter "/" occuring once, but in my case, it could happen that I have in the same cell, the occurence of this situation several times, do you have any idea how can I fix that? 

Many thanks again!

[Nathaniel_C · Accepted solution]

Hi @Betty888 ,

You can choose split at each occurence, or you can choose the rightmost or the leftmost occurrence.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel