replace EACH Nth occurrence a character in a string with another character

**mattman7** · 06-22-2016, 04:55 PM

I've seen related posts, but none had an answer I can use on my Mac. I can't seem to use regex find/replace on my mac version. I can't use VBA, only an excel formula.

Problem: I need to find EACH 2nd occurrence of the string "</li>" and replace it with "</li></ul></div><div class="fourCol"><ul>". The maximum number of insertions is 3, which will create a 4 column grid.

Ex string:
<div class="bulletBlock"><div class="fourCol"><ul><li>Chrysler Aspen</li><li> Dodge Durango</li><li> Infiniti FX35</li><li> Infiniti QX70</li><li> Infiniti FX45</li><li> Infiniti FX37</li><li> Jeep Grand Cherokee</li></ul></div></div>

Desired outcome:
<div class="bulletBlock"><div class="fourCol"><ul><li>Chrysler Aspen</li><li> Dodge Durango</li></ul></div><div class="fourCol"><ul><li> Infiniti FX35</li><li> Infiniti QX70</li></ul></div><div class="fourCol"><ul><li> Infiniti FX45</li><li> Infiniti FX37</li></ul></div><div class="fourCol"><ul><li> Jeep Grand Cherokee</li></ul></div></div>

**JohnTopley** · 06-22-2016, 05:42 PM

Maximum number of occurrences of </li> ???

**FlameRetired** · 06-22-2016, 06:21 PM

Withdrawn by FR.

**Phuocam** · 06-22-2016, 08:42 PM

Cell B1 = </li></ul></div><div class="fourCol"><ul>

Maybe

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"</li>",B1,10),"</li>",B1,8),"</li>",B1,6),"</li>",B1,4),"</li>",B1,2)

**mattman7** · 06-22-2016, 08:58 PM

Originally Posted by JohnTopley

Maximum number of occurrences of </li> ???

Maximum number of find/replace points is 3. This will result in a 4 column grid.

**FlameRetired** · 06-22-2016, 08:59 PM

mattman7 welcome to the forum.

I agree with John Topley. We need more information.

An uploaded Excel spreadsheet file with enough examples usually helps. That way the data is there, we don't have to re-type / copy / past / format ...... If it doesn't answer our questions at least we will know what the questions are.

**mattman7** · 06-22-2016, 09:27 PM

Attached is a sample file. Extra instructions at top of file..
I hope this helps..

**mattman7** · 06-23-2016, 11:36 AM

added uploaded file with additional instructions.

**mattman7** · 06-23-2016, 11:55 AM

Originally Posted by Phuocam

Cell B1 = </li></ul></div><div class="fourCol"><ul>

Maybe

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"</li>",B1,10),"</li>",B1,8),"</li>",B1,6),"</li>",B1,4),"</li>",B1,2)

This won't work for me as each row has a different number of <li> items. Please see the sample file attached.
thanks!

**JohnTopley** · 06-23-2016, 12:23 PM

In I4 there are 24 "</li> ????

Based on your first post:

<div class="bulletBlock"><div class="fourCol"><ul><li>Chrysler Aspen</li><li> Dodge Durango</li><li> Infiniti FX35</li><li> Infiniti QX70</li><li> Infiniti FX45</li><li> Infiniti FX37</li><li> Jeep Grand Cherokee</li></ul></div></div>

Try on the above:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"</li>","***",2),"</li>","***",3),"</li>","***",4),"***",B1)

I used "***" just make it easier (for me) to see the substitutions

B1 contains </li> </ul> </div> <div class="fourCol"> <ul>

You will need many more substitutions for I4 , assuming I understand your requirement correctly.

replace EACH Nth occurrence a character in a string with another character

LinkBack

Thread Tools

Rate This Thread

Display

replace EACH Nth occurrence a character in a string with another character

Re: replace EACH Nth occurrence a character in a string with another character

Re: replace EACH Nth occurrence a character in a string with another character

Re: replace EACH Nth occurrence a character in a string with another character

Re: replace EACH Nth occurrence a character in a string with another character

Re: replace EACH Nth occurrence a character in a string with another character

Re: replace EACH Nth occurrence a character in a string with another character

Re: replace EACH Nth occurrence a character in a string with another character

Re: replace EACH Nth occurrence a character in a string with another character

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Split data string into 2 new cells after 4th occurrence of character

How can I replace a character in a string ?

[SOLVED] Getting the string that follows the last occurrence of a character

[SOLVED] find the character position in a string of the last occurrence of a nominated character

Find the last occurrence of a character in a string

Cutting off a string at the last occurrence of a character

Replace only one character in string.

Bookmarks

Bookmarks

Posting Permissions