Replacing A Dot In String After A Specific (KeyWorded

[indi visual]

Here is a tricky one.


Good ".me" = "(iandmyself.me)"

Bad ".me" = "(KeyWorded problematic value.me)"


How do I ONLY change the bad dot in "(KeyWorded problematic value.me)" to another character (or word)?


It's tricky because I won't know what any of this text is other than ".me" and "(Keyworded".


These are the only two definitives I have to go off, I can't figure out how to change the bad .me without accidentally changing the good .me (and vice versa).


Everything else including value structure-location, text, and length of string could be anything in various places and rows.


My ultimate goal is to trap "(iandmyself.me)" and delete it without disturbing anything else in this crazy string. My logic is that if I remove the dot, I can then tell them apart enough to delete what I want without accidentally deleting both of them. I am welcome to a better recommended strategy if suggested.


Here is the monster:

unencapsulated value (value with spaces) (nospacevalue) (KeyWorded problematic value.me) (iandmyself.me) (KeyWorded problematic value.me) (nospacevalue) (value with spaces) unencapsulated value

 

[indi visual]

Resolved!


Thanks Hui you're awesome!


Sub DifferentiateDots()

Application.ScreenUpdating = False

Dim c As Range

Dim b1 As Integer

Dim b2 As Integer

Dim midstring As String

Dim MyString As String


For Each c In Range(Cells(3, 1), Cells(3, 1).End(xlDown))

MyString = c.Value


If InStr(1, MyString, "(") > 0 And InStr(1, MyString, ")") > 1 And InStr(9, MyString, "KeyWorded") > 1 Then

b1 = InStr(9, MyString, "(KeyWorded")

b2 = InStr(b1, MyString, ".")

midstring = Mid(MyString, b1, b2 - b1) + "DistinguishMarker"

MyString = Left(MyString, b1 - 1) & midstring & Right(MyString, Len(MyString) - b2 + 1)

c.Value = MyString


End If

Next

Application.ScreenUpdating = True

End Sub

 

[Hui]

Indi


In this case if you can't describe the problem how can we be expected to assist.

As I've said previously there has to be rules and if you don't know or can't explain them we can't be expected to give you good results.


You keep asking us for small bits of help in a much larger problem

I have a feeling that if you told us the whole story then the solution would probably be much better than the combination of components we keep feeding you.

 

[indi visual]

My ultimate ultimate ultimate goal is to trapped all links in my worksheet with braces, and then delete them {*}.


{http://www.iandmyself.me}


Some links have ID URLStartMarkers eg:

http

www etc..


All links have ID URLExtentions

.com

.net

.me etc..


I have to drop a brace in before every {start marker, and every end Extention marker}.


So far I've been successful, just running into a few last snags that can't find a few urls for random case reasons. The code I posted I above is one those random reasons.


I can knock out 5,000 links in a second (with no unecessary text accidentally deleted I might add, and all of these links vary in random ways).


I've come a long way with your help. There's about 20 more links I'm trying to figure out how to kill, but with this posted code above I think I can get them.


The components are helping tremendously. I ask for them in pieces largely because I find a faster way to do something... after I find a faster way to do something.


My ultimate goal is and has always been removing urls with string manipulation.


Actually I received an error message with above code I thought would work.


It works fine, but if a dot is not present in "(KeyWorded problematic value.me)", then it bugs out.


My whole point for this code was to find this dot in the first place, and I like I said it works, but the entire macro bugs out if all of the "(KeyWorded problematic value.me)" don't have a dot (actually some will and some won't).


Could you please assist me with an if that states if a dot doesn't exist to skip it?

 

[indi visual]

Please have a look. The expected results sheet doesn't have those extra spaces like last time. There are buttons for everything.


http://www.iandmyself.me/Kill.Links.Epitome.Example.xlsm


I can't imagine a more full story than this link.


These are carefully created messy url strings in every possible way that can exist in a string. In theory, if I nail this, there isn't a possible url I won't be able to find and delete.


Extra Note:

I won't know what a single word of text will be, all text used in this workbook are generic word structures, and the location of where they are in the string are generic as well. The only thing I have to go off of is "http", "www.", and the 252 url extentions (although I'm only using 1 for this workbook which is ".me".)


Also, about that "problematic value stuff":

I intentionally placed that there to find a work around when something is encapsulated and has a similar URL Extention Marker as a real url extention (but actually in fact is not a link). If I hadn't done this, I would've accidentally killed it with the real links. So I placed those there to put my code to the true test of killing only what it has to.

 

[Hui]

Indi


I copied the data in Expected Results into a column besides Column A in Resource Locators Removed and now have the following queries

[pre]

Row 12:
Col A:(iandmyself.me)- _ - _ - -- __ -- __ -- (({(KeyWord problematic valueSafeMarkerSafeMarkerSafeMarker.me)
Col B:(KeyWord problematic value.me)

Row 27:
Col A: (KeyWord problematic value.me) (iandmyself.me)- _ - _ - -- __ -- __ --
Col B: (KeyWord problematic value.me)

Row 65:
Col A: unencapsulated value (value with spaces) (nospacevalue) (({(KeyWord problematic valueSafeMarkerSafeMarkerSafeMarker.me) (KeyWord problematic value.me) (nospacevalue) (value with spaces) unencapsulated value  _ - _ - -- __ -- __ --
Col B: unencapsulated value (value with spaces) (nospacevalue) (KeyWord problematic value.me) (KeyWord problematic value.me) (nospacevalue) (value with spaces) unencapsulated value

[/pre]

These seem inconsistent with each other

ie: Why isn't Row 12 (iandmyself.me) suitable as according to Row 65 it should be ?

 

[indi visual]

I'm not sure I understand your query.


Strings are in 5 groups:

1. basic structure with problematic value on the end of string

2. basic structure with problematic value at the start of string

3. with link located in the middle of string

4. with link located at the start of string

5. with link located at the end of string


I wrote the worst case scenario for each group in a patterned order.


Examine the pattern first before running my code on it.


In fact, my code simply nip picks at different ways to kill the links with no real organized strategy in place.


I simply left the module in the workbook so you could kind of somewhat see what I was up to.


Reset the sheet first, and then compare both Column A's next to each other.


Expected results is my objective.

 

[Hui]

Indi

please send me your email address


my email is at the bottom of

http://chandoo.org/wp/about-hui/

 

[indi visual]

I emailed it, thx Hui as always.