Replacing Part of Text with Another Part of Same Cell

Meatballsub

New Member
Joined
Feb 21, 2018
Messages
7
Hello!

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

<a *******="viewer.setParameter('SceneUrl', 'units/models/nomodel.stl');viewer.init(); viewer.update();" >Unit-Model-Here</a>

<tbody>
</tbody>

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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
WO1V73u
problem.jpg

Really sorry for triple posting
WO1V73u
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.
 
Upvote 0
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.
Code:
[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]
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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).

problem2.jpg

problem3.jpg
 
Upvote 0
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).

problem2.jpg

problem3.jpg
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.
 
Last edited:
Upvote 0
Absolutely, and thanks again for help.

<a *******="viewer.setParameter('SceneUrl', 'units/models/nomodel.stl');viewer.init(); viewer.update();" >E180-A-CK, 5:1 mod (M7)</a>

<a *******="viewer.setParameter('SceneUrl', 'units/models/nomodel.stl');viewer.init(); viewer.update();" >B200-F-CP</a>

<tbody>
</tbody>

<tbody>
</tbody>


<tbody>
</tbody>
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top