Add trim after Split

[Anonymous]

I am using this to Find/Replace a table

let
   Repl = Table.Buffer(ReplacementsTable),
   Text = Text,
    Result = Table.AddColumn(Text, "Changed Text Expected",
    each Text.Combine(
    List.Transform(
    Text.Split([Text], "|"),
    each
    try Repl[Replace With]{List.PositionOf(Repl[Word To Replace], _)}
    otherwise _
    ),
    "|" )
)
[[Changed Text Expected]]
in
Result

I am splitting a column on a pipe delimiter

Is it possible to add trim at this point?

Text.Split([Text], “|”),

I have strings that look like

the|cat sat |on the mat

find replace
cat sat >> bear

does not get replaced because of the space after the "sat" in “the|cat sat |on the mat”

Thanks

[AlexisOlson]

You can replace 

Text.Split([Text], "|")

with

List.Transform(Text.Split([Text], "|"), Text.Trim)

to trim each string after separating by "|".

Here's another implementation that uses List.ReplaceMatchingItems:

let
  Repl = Table.Buffer(ReplacementsTable), 
  Text = Text, 
  Result = Table.AddColumn(
    Text, 
    "Changed Text Expected", 
    each Text.Combine(
      List.ReplaceMatchingItems(
        List.Transform(Text.Split([Text], "|"), Text.Trim), 
        List.Zip({Repl[Word To Replace], Repl[Replace With]})
      ), 
      "|"
    )
  )
in
  Result

[Vijay_A_Verma · Accepted solution]

Use below in place of Text.Split([Text], "|")

Text.Split(Text.Replace(Text.Replace([Text]," |","|"),"| ","|") "|")

[Anonymous]

Thank you. This worked great!

[AnkitKukreja]

@Anonymous 

Do you have limited scenarios then maybe try replacing value, else could you please share some sample data (5-10) rows and the expected output.