Replacing Part of Text with Another Part of Same Cell

[Meatballsub]

Hello!

I'm hoping someone can help me with my current problem. I have thousands of cells that look like this:

[TABLE="width: 983"]
<tbody>[TR]
[TD="width: 983"]<a *******="viewer.setParameter('SceneUrl', 'units/models/nomodel.stl');viewer.init(); viewer.update();" >Unit-Model-Here</a>[/TD]
[/TR]
</tbody>[/TABLE]

What I'd like to do is replace the "nomodel" text above with whatever is between <a....></a> (in this case, it is Unit-Model-Here).

I've looked in other threads and have tried to adapt solutions from there, but have yet to figure out a working one. Any help would be much appreciated.

 

[Meatballsub]

Apologies for the example format screwing up. Hopefully this appears better.

Unit-Model-Here

 

[Meatballsub]

Really sorry for triple posting

but it appears the only way the code will properly show up is if I include an image, which is attached. Sorry again for the triple post.

 

[Rick Rothstein]

I have assumed your values are in Column A starting on Row 1 and I put the output in Column N although if your aim is to output the corrected data over top of the original data, you can change the N1 reference in the last line of code to A1.

[table="width: 500"]
[tr]
	[td]Sub ReplaceNoModelText()
  Dim R As Long, Data As Variant, Parts() As String
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    Parts = Split(Data(R, 1), ">")
    Data(R, 1) = Replace(Data(R, 1), "/nomodel.", "/" & Parts(UBound(Parts) - 1) & ".")
  Next
  Range("N1").Resize(UBound(Data)) = Data
End Sub[/td]
[/tr]
[/table]

 

[Meatballsub]

Thank you so much! It appears that it works great up until line 276, and then it includes (less than symbol, slash symbol, and an a) at the end of the replaced text. I typed out what is in parenthesis because it wasn't formating properly in the post.

 

[Rick Rothstein]

Thank you so much! It appears that it works great up until line 276, and then it includes (less than symbol, slash symbol, and an a) at the end of the replaced text. I typed out what is in parenthesis because it wasn't formating properly in the post.

Then your data starting at line 276 is not structured the same as the lines of text before it. Show me what is in line 276 and I'll see if I can spot the difference for you.

 

[Meatballsub]

Sorry, the text between the A tags definitely varies throughout the sheet. Here is line 276 and then one from further down that is pretty generic (though still has the same issue).

 

[Rick Rothstein]

Sorry, the text between the A tags definitely varies throughout the sheet. Here is line 276 and then one from further down that is pretty generic (though still has the same issue).

I'd rather not type those out... can you post the actual lines of text like you did in the first message? Don't worry that it does not show up correctly... I can get to the actual posted lines elsewhere.

Also, tell me what text is supposed to be used from each of those to replace the 'nomodel' text.

 

[Meatballsub]

[TABLE="width: 983"]
<tbody>[TR]
[TD="class: xl65, width: 983"]Absolutely, and thanks again for help.

[TABLE="width: 983"]
<tbody>[TR]
[TD="class: xl65, width: 983"]<a *******="viewer.setParameter('SceneUrl', 'units/models/nomodel.stl');viewer.init(); viewer.update();" >E180-A-CK, 5:1 mod (M7)</a>

[TABLE="width: 983"]
<tbody>[TR]
[TD="class: xl65, width: 983"]<a *******="viewer.setParameter('SceneUrl', 'units/models/nomodel.stl');viewer.init(); viewer.update();" >B200-F-CP</a>[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Absolutely, and thanks again for help.

Hmm! I don't get the error that you reported earlier... the replacement for both worked as you indicated you wanted. However, I do note the first one will not worki when you try to use it as the text contains a colon and a colon is not allowed in a filename. Perhaps that first one should stop at the comma? But in neither case does my code produce the odd result you reported earlier. Maybe there are some odd, non-visible characters in the file that is messing things up. Can you post a copy of your workbook to DropBox so that we can download the actual file with your actual data in order to test solutions against?