Remove last <li> entry from cells

  • Thread starter Thread starter Easton King
  • Start date Start date
E

Easton King

I have a large xls doc and some cells have entries that end with
<li>. I want to remove only this last instance of <li> and only when
it is the last thing in the particular cell.
For example:
<li>
Blah blah blah</li>
<li>
Blah blah blah</li>
<li>
Blah blah blah</li>
<li>

In this case, I want to remove only the last instance of the <li>

I have ASAP utils also, but can't seem to figure the logic to make
this happen.

TIA for any help you may be able to provide.
 
Try this copied exactly as shown and changing column I to suit

Sub removelastitem()
For i = Cells(Rows.Count, "i").End(xlUp).Row To 2 Step -1
If Len(Cells(i, "i")) > 4 And _
Right(Cells(i, "i"), 4) = "" < lid > "" Then
Rows(i).Delete
Exit For
End If
Next i
End Sub
 
lid should be li. Somehow it got changed in the transmission (probably
winmail spell check)
 
It was pointed out correctly to me by Sandy that this should have had only
one set of quotes.
That is correct IF I hadn't copied the text below which had the / in the
data giving</li> on my reader. When I go back and type in <li> then it
should be "<li>" but with what I copied excel changed to ""< li >"".??????
Try it both ways and see what you get.
Again, I do not mind being questioned or criticized in the post. Harlan
delights in finding my errors.

Sub removelastitem()
For i = Cells(Rows.Count, "i").End(xlUp).Row To 2 Step -1
If Len(Cells(i, "i")) > 4 And _

Right(Cells(i, "i"), 4) = "<li>" Then ' <li>
'Right(Cells(i, "i"), 4) = "" < li > "" Then ' </li> as tested

Rows(i).Delete
Exit For
End If
Next i
End Sub
 
mmmmm.....

Its a matter of interpretation - not my strong suit usually. My take is
that when the OP said:
In this case, I want to remove only the last instance of the <li>
Click to expand...

they wanted the </li> left alone.

Doing a Google search for <li> and </li> it seems, (I didn't know), that
they are HTML tags for lists as in:

<li> Item 1 </li>
<li> Item 2 </li>

I assumed therefore that the whole of the data provided by the OP was in one
cll and the extra lone <li> 's were what the OP wanted removed.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Assuming that all the data given was in one cell then to remove the extra
HTML Tags try:

Sub DeleteTag()
For Each cell In Selection
cell.Value = Application.Substitute(cell.Value, "<li>" & Chr(10),
"")
Next cell
End Sub

Perhaps the OP will enlighten us as to what he/she wants but in any case I'm
off to bed.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
No that left the last <li> in place try:

Sub DeleteTag()
For Each cell In Selection
cell.Value = _
Application.Substitute(cell.Value, "<li>" & Chr(10), "")

If Right(cell.Value, 4) = "<li>" Then _
cell.Value = Left(cell.Value, Len(cell.Value) - 4)
Next cell
End Sub


Now I really am off to bed!
--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
It's no good I forgot to test Don's code and I wouldn't be able to get to
sleep for thinking about it.

Don said:
'Right(Cells(i, "i"), 4) = "" < li > "" Then ' </li> as tested
Click to expand...

I find that I can't enter the </li> sequence with double quotes without VBA
telling me that it expects an expression, (but it will however accept it
with single" quotes but then it does nothing). I also find that ""<li>"" in
that line removes *any* line except the single <li> lines try running your
code repeatedly on:

<li>
Blah blah blah</li>
Don
<li>
Blah blah blah</li>
Sandy
<li>
Blah blah blah</li>
<li>


Mind empty - practicing meditation - at peace with the world -and off to
bed.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Here is my take on the question of double quotes:

The line:

If Len(Cells(i, "i")) > 4 And _
Right(Cells(i, "i"), 4) = "<li>" Then ' <li>

changes to:

If Len(Cells(i, "i")) > 4 And _
Right(Cells(i, "i"), 4) = "" < li > "" Then ' <li>

when additional quotes are added. Why does VBA add the extra spaces and
what difference do they make?

Well, I think that VBA is interpreting * "" < li * as: An empty string is
smaller than an undeclared variable called li with a zero value, which is
TRUE.

VBA substitutes TRUE and the line then reads:
Right(Cells(i, "i"), 4) = TRUE > "" Then ' <li>

Where TRUE, which XL equates of a value of 1, is greater then an empty
string with a value of zero. This again is TRUE, so this line therefore
passes for *any* value in the cell with a length longer than 4

This is borne out by the fact that if you put Option Explicit at the top of
the code, (as I have read here always a good thing to do), and DIM the
variable i, VBA objects to the undeclared variable li.

What I don't understand, and perhaps someone who has a greater understanding
of VBA them me could explain, is, if the above is TRUE, then why does:

Right(Cells(i, "i"), 4) = TRUE Then ' <li>

not work or does VBA have to coerce the TRUE value itself?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
A reiteration of what I did (Vista HP, xl2003 up to date)

I copied the list below into col I
I copied the LAST item which was <li> and got no deletion using "<li>"
I copied the next to last item and removed the preceding text to get "</li>"
Somehow, excel changed to ""< li >""
But NOW, I can't replicate.???? Hopefully the OP will tell us the desired
deletion
 
A reiteration of what I did (Vista HP, xl2003 up to date)

I copied the list below into col I
I copied the LAST item which was <li> and got no deletion using "<li>"
I copied the next to last item and removed the preceding text to get "</li>"
Somehow, excel changed to ""< li >""
But NOW, I can't replicate.???? Hopefully the OP will tell us the desired
deletion
Click to expand...

The intended output for this issue would be:

<li>
Blah blah blah</li>
Don
<li>
Blah blah blah</li>
Sandy
<li>
Blah blah blah</li>
 
Is the data that you gave:

<li>
Blah blah blah</li>
<li>
Blah blah blah</li>
<li>
Blah blah blah</li>
<li>

all in the one cell? I am going to assume that it is and that the final <li>
may or may not have a line break after it.

Copy the code below and then press the Alt and the function key F11 together
to take you into VBA, then select Insert > Module and in the white module
sheet paste this code into the module:

Sub RemoveIt()
For Each cell In Selection
If Right(cell, 4) = "<li>" Then
cell.Value = Left(cell.Value, Len(cell) - 4)
End If
If Right(cell, 5) = "<li>" & Chr(10) Then
cell.Value = Left(cell.Value, Len(cell) - 5)
End If
Next cell
End Sub

Now in the worksheet select the cells that you want to remove the <li> from
and select Tools > Macro > Macros and click Ok.

This will remove the last <li> from the cells whether or not there is a line
break after it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
It is very rude to ask a question that others are trying to answer for you
and for you to ignore their requests for information.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don Guillett said:
Re state your desired outcome. There is confusion about the / within
the <li>
Click to expand...
 
It is very rude to ask a question that others are trying to answer for you
and for you to ignore their requests for information.
Click to expand...

I agree that this is rude. That is why I am trying to monitor this
group. I'm sorry that I was called away from the newsgroup for a
couple of hours...Your post is again asking for a re-statement that
has already been made. I do not know how to make this clearer...
The last thing that is in this cell should be </li>. I am trying to
remove the last <li> that appears in the op.
 
Easton King said:
The last thing that is in this cell should be </li>. I am trying to
remove the last <li> that appears in the op.
Click to expand...


Isn't that just what my suggested code does?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Isn't that just what my suggested code does?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...

It does indeed. Thanks so much. Now to go back and figure out how
that worked.....
I appreciate your help and apologize for the delay in responding.
Thanks again!
 
You are very welcome, I'm glad that we got there in the end. <g>

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top