Extract Text After Delimeter but not the ACTUAL Delimeter?

[sscanlon]

I cannot find an answer anywhere to this so not sure it's even supported, but seems like such an easy thing to achieve. 

I have a string it looks like this 

Some random text I want to remove [Pr. Svc.] The text I want 

I want to keep the [text] This text here that is in red above

{{"Prod_Name", each Text.AfterDelimiter(_, " [", {0, RelativePosition.FromEnd}), type text}})

That is what I am using, and works fine but ends up like this, (and I try a 1 instead of 0 and other options and cannot figure it out)

Pr. Svc.] The text I want 

I want the [  at the beginning

Any thoughts would be much appreciated! 

Shawn

[v-kkf-msft · Accepted solution]

Hi @sscanlon ,

Please try to combine the "[" symbol with the split column.

= "[" & [Prod_Name.2]

Or just add a custom column:

= try Text.Range([Prod_Name], Text.PositionOf([Prod_Name],"[")) otherwise null

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sscanlon]

Your custom column worked perfect, was easier to do that single step and returned exactly what I needed, thank you very much! 

[bchager6]

Yes, zero zero A zero. Here's all my code:

let
Source = Excel.Workbook(File.Contents("C:\Users\xxxx\OneDrive - xxxx\Desktop\Book1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Column 1", Splitter.SplitTextByDelimiter("#(00A0)", QuoteStyle.Csv), {"Column 1.1", "Column 1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column 1.1", type text}, {"Column 1.2", type text}})
in
#"Changed Type1"

[sscanlon]

Unfortunately still not working, thank you for trying though I really appreciate it. 

Must be something wrong with my column or maybe my merge method earlier. Will play with it later and try to reply back.

[bchager6]

Do you want to just keep [Pr. Svc.] or [Pr. Svc.] The text I want?

If just the [Pr. Svc.], that's pretty straightforward:

Try using the Column from Examples function. 

• Highlight the column you want to transform
• From the Add Column ribbon, select the Column from Examples drop-down
• Select, "From Selection"
• A new, blank column will appear
• Type [Pr. Svc.]
• Click OK

[sscanlon]

Hi thank you for the help 

I need to keep [Pr. Svc.] The text I want 

Issue is - it is these 3 at start

[Pr. Svc] 

[Sh. Svc]

[Comm.]

The a space followed by random text (different for each one of the above). 

So not sure examples function would work? 

[bchager6]

Try inserting this in to your advanced editor:

#"Inserted Text After Delimiter" = Table.AddColumn(#"Your previous step name", "Text After Delimiter", each Text.AfterDelimiter([Your column name], " "), type text)

[sscanlon]

Hi, that worked and grabs the text after [xxxxx] but is just that text 

It is strange that text before or after delimeter removes the actual delimeter, is this normal? 

I am saying that I want to extract AFTER the delimeter, not including it? 

[bchager6]

Split Column by Delimiter worked for me too:

[bchager6]

Do you want your results to look like what I have in the Text After Delimiter column? I'm confused because earlier you wrote that you want to keep "[Pr. Svc.] The text I want" and now you're saying you want to extract after the delimiter, not including it. What delimiter?

[sscanlon]

sorry for confusion, I want exactly what you show there, but it will make more sense with my image here. I don't get what you do, I must be setting something wrong? 

[bchager6]

If your settings match mine, I think it's going to work:

[sscanlon]

I tried but just shows null for 2nd column

Is that a zero zero A zero? I am not familiar with Hex? for characters.

Thanks again for all the help