ending "html" or change

[ppeer]

Hi Expert,

I have to check if webadresses (of different sizes) in column A end on
"html" (e.g. www.variablestrings/buy/hold/details.html)
Sometimes, the end is more like html -25, or html 9 or html r7, or
whatever.
I would like to clean up all cell content after html
But how? Tried some already, but still in the mist...

best regards Peter

 

[Chip Pearson]

It isn't clear what you are asking for, but the following might get
you going in the right direction.

Using Formulas: Test in A1 ends in "html"
=RIGHT(A1,4)="html"
return TRUE or FALSE

Remove text in A1 after "html"
=IF(RIGHT(A1,4)="html",A1,LEFT(A1,FIND("html",A1)+3))

Using VBA:

Dim S As String
Dim T As String
Dim N As Long
S = Range("a1").Value
N = InStr(1, S, "html", vbTextCompare)
If N Then
T = Left(S, N + 3)
Else
T = S
End If
Debug.Print T

S gets the value of cell A1. N = 0 means "html" not at end of S. N <>
0 means "html" at end of S. T gets text in A1 before the "html"
string.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]