+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472

    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

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,655
    Why not just change the first instance of | to §, and split on §?

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

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    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

  4. #4
    Forum Contributor
    Join Date
    11-12-2007
    Location
    Germany
    MS-Off Ver
    2007
    Posts
    472
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1