aPos bPos, Replace bPos With Character ( Vice Versa)

[indi visual]

I have a pretty good idea who will help me with this.


I think we should all go jet-ski fishing some time.


It's a lot of fun if you can get past the ridiculous part (jk).


This is my very very last and final request on this issue.


I know this is a place to ask questions, but I know I must get on people's nerves (lol).


I mean, I get on my own nerves asking the questions sometimes so I can only imagine...


Anyway here goes...


Could someone please write (help) me with 2 codes that does the following:


''code that finds and replaces the first appearing space right AFTER "various" with an end brace so it would change to "{various}text"


Example random {various text in string * missing end brace


-And A Code For Vice Versa-


''code that finds and replaces the first appearing space BEFORE "various" with an open brace so it would change to "random{various}"


Example random various} text in string * missing open brace


I was hoping to get this code in two pieces because I plan to use it seperately at different times:


One code to search for the first space just BEFORE the end brace,

and another to search for the first space just AFTER the open brace.


Extra Note: I will have my braces already in place before you provide me this code, I can get them in the strings just fine. It's just some of them have fronts with no ends, and some of them have ends with no fronts, so the best way I know how to cap them off is this way.


My Objective: To simply encapsulate words meeting criteria I know should not have a space (ie you guessed it... urls).


The End.

 

[Hui]

an all in 1 solution

[pre]

Sub fix_various()

Dim c As Range
Dim MyString As String

Worksheets("Sheet1").Activate

For Each c In Range(Cells(2, 1), Cells(2, 1).End(xlDown))
MyString = c.Value

If InStr(1, MyString, "{various ") > 0 Then
MyString = Left(MyString, InStr(1, MyString, "{various ") - 1) & "{various}" & Right(MyString, Len(MyString) - InStr(1, MyString, "{various ") - 7)
c.Value = MyString

ElseIf InStr(1, MyString, " various}") > 0 Then
MyString = Left(MyString, InStr(1, MyString, " various}")) & "{various}" & Right(MyString, Len(MyString) - InStr(1, MyString, "{various ") - 13)
c.Value = MyString

End If

Next

End Sub

[/pre]

But once again you can use this on the array you have already loaded

so just update the array values if required and then it will all get written out at once

 

[indi visual]

Various is just a random word I used for this post example.


I'll never know what various is.


Various could be:


{HttpVariousBlahBlah

-or-

BlahBlahVarious.com}


I place the first brace against the part in the string I can identify as a partial link match, and I was needing a code that finds the space on the opposite side to cap it off.


I'm only going to know one or the other, which is why I need to replace the space at the start or end with a brace.


The space is important because it's the only thing I can positively ID right in the right location (because BlahBlah could be anything).


The space represents the only thing I have to go off of on the opposite side. Because url links don't have spaces, which means a space represents the start or end of a url.


As for asking for 2 separate codes:


An all in one is fine so long as I doesn't produce double braces {{ }}. Because I will already have a few complete brace sets scattered throughout my sheet.

 

[Hui]

Messed up that interp didn't I

But this should make up for that

[pre]

Sub fix_various()

Dim c As Range
Dim b1 As Integer
Dim b2 As Integer
Dim midstring As String
Dim MyString As String

Worksheets("Sheet1").Activate

For Each c In Range(Cells(2, 1), Cells(2, 1).End(xlDown))
MyString = c.Value

If InStr(1, MyString, "{") > 0 And InStr(1, MyString, "}") = 0 Then
b1 = InStr(1, MyString, "{")
b2 = InStr(b1, MyString, " ")
midstring = Mid(MyString, b1, b2 - b1) + "}"
MyString = Left(MyString, b1 - 1) & midstring & Right(MyString, Len(MyString) - b2 + 1)
c.Value = MyString
ElseIf InStr(1, MyString, "{") = 0 And InStr(1, MyString, "}") > 0 Then
b2 = InStr(1, MyString, "}")
For i = b2 - 1 To 1 Step -1
If Mid(MyString, i) = " " Then
b1 = i
Exit For
End If
Next

midstring = "{" + Mid(MyString, b1, b2 - b1)
MyString = Left(MyString, b1 - 1) & midstring & Right(MyString, Len(MyString) - b2 + 1)
c.Value = MyString
End If
Next

End Sub

[/pre]

 

[indi visual]

How long have you been doing this?


That was way too fast.


People who can do stuff like this should have super models making them breakfast every morning ; )


Resolved.

 

[Hui]

and I don't ?

 

[indi visual]

Quick question...

Keep getting a debug error here:


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


I'm sure it's a quick fix but I can't figure it out.


This is suppose to place the missing end-brace on the left or right side.


I've been testing it with this line with no luck.


Example blah blah stringbase.org}


Once the above code is ran it's supposed to look like this.


Example blah blah {stringbase.org}


Hui man I apologize I could've sworn I had this working (I didn't modify anything, it's just bugging out on me).

 

[indi visual]

If Mid(MyString, i, 1) = " " Then


Found it fixed it (the "1" was missing).