Change second "|" sings into "§" , third "|" into "§§" and fourth into "§§§"...

Hi,

i want to spit text of each Cell of Column A into many columns. The signe through which i want to split one column into two is"|". But in some cells this signe is twice or more times there which makes it problematic to use the excel function "text into columns".

How can i keep the first "|" signe in a cell and convert all following "|" signs into "§"? Second "|" in the cell to "§", third to "§§", fourth to "§§§"...

Before:
Column A
love|amore,liebe,|lubov,mina|,muhabbat

After:
ColumnA
love|amore,liebe,§lubov,mina§§,muhabbat

Thanks for each assistance

Why not just change the first instance of | to §, and split on §?

=SUBSTITUTE(A2, "|", "§", 1)

Hello Wali,

If you're still interested in the macro to do this, here is the code. This starts in cell "A2" and continues down the column to the last entry. You can change the starting cell in the macro by changing the variables "C" and "StartRow". They are marked in red.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Dear Shg and dear Leith Ross,
thank you very much for your help. It worked. Both of the solutions. That will save me lot of work. Thanks alot you both