A Field gives me the following string:
#$Mercury#$Venus#$Earth#$Mars#$
My goal is to remove the #$-chars and separate the text with comma and space. It should look like this:
Mercury, Venus, Earth, Mars
What I tried to do:
My code:
=Replace(
Left(
Right(
First(Fields!FieldX.Value, "DATASET"), Len(First(Fields!FieldX.Value, "DATASET"))-2),
Len(First(Fields!FieldX.Value, "DATASET"))-2),
"#$",", "
)
This gives me:
Mercury, Venus, Earth, Mars,
At the end after Mars there is a comma and it shouldn't be there. I don't know how to do that. Maybe the MID function is the better solution, but then I don't understand how it can be used when the strings are of variable length.
Thanks in advance.
This should work. I tested in a table so you will have to add the FIRST
and "DATASET"
bits back in )
=JOIN(
SPLIT(MID(Fields!SampleText.Value, 3, LEN(Fields!SampleText.Value)-4), "#$"),
",")
All we do here is chop off the first and last 2 characters, then SPLIT
on each instance of #S
which gives us an array. Then JOIN
the array elements back togther using ,
as the delimiter.