Replace every 3rd comma with &

[prat1507]

Hi I want to replace every third comma in my string with '&' in a text box, can someone help me please?

Regards

Pratyush

[marcus_sommer]

Maybe with something like this:

mid(Field, 1, index(Field, ',', 3) - 1) & '&' & mid(Field, index(Field, ',', 3) + 1)

- Marcus

[vishsaggi]

Can you share how your string looks like?

[prat1507]

Hi Marc

This works only for the 3rd comma, I want it on 3rd, 6th, 9th and so on.....on every 3x comma.

Regards
Pratyush

[prat1507]

Take any comma separated string.

Regards

Pratyush

[sunny_talwar · Accepted solution]

May be this

=Concat(Mid(Dim,

Index(Dim, ',', 3 * (ValueLoop(1, Floor(SubStringCount(Dim, ',')/3)+1)-1)) + 1,

(If(Index(Dim, ',', 3 * (ValueLoop(1, Floor(SubStringCount(Dim, ',')/3)+1))) = 0, Len(Dim), Index(Dim, ',', 3 * (ValueLoop(1, Floor(SubStringCount(Dim, ',')/3)+1)))-1)-Index(Dim, ',', 3 * (ValueLoop(1, Floor(SubStringCount(Dim, ',')/3)+1)-1))))

, '&', ValueLoop(1, Floor(SubStringCount(Dim, ',')/3)+1))

[prat1507]

Thanks Sunny, worked as a miracle.

Regards

Pratyush