Trim HTML OuterText

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,054
Office Version
  1. 2016
Platform
  1. Windows
I am trying to trim some text from some html, but can not work it out. I have never been good at triming as I don't full understand it. The text will always be between the first two forward slashes "/" in this case the text is "webdevlondon".

The data (Html) will go into the column B as it is FOUND and then I want to clean it up with a trim only leaving the name. It is currently not in the column, therefore the procedure has to be called after the data as gone into a cell in column b

OuterHTML
HTML:
<A class=_2yau href="about:/webdevlondon/?ref=page_internal" data-endpoint="/webdevlondon/?ref=page_internal"><SPAN class=_2yav>Home</SPAN><SPAN role=progressbar aria-busy=true aria-valuetext=Loading... class="img _55ym _55yn _55yo _2wwb" aria-valuemin=0 aria-valuemax=100></SPAN></A>

I was tying to use this, data will be in row 2 and dynamic lenght. If possible I would like it done with vba and it seems to run faster I have tried TRIM process in the past and they seem to be much slower.

VBA Code:
Set wb = ThisWorkbook
Set wsSheet = wb.Sheets("Data")
   wsSheet.Columns(b).Value = Left(myString, InStr(2, myString, "/", vbTextCompare) - 1) ' trim items on right
   wsSheet.Columns(b).Value = Right(myString, InStr(2, myString, "/", vbTextCompare) - 1) ' trim items on left
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,304
Office Version
  1. 2010
Platform
  1. Windows
heres a little function that will do the trim. to use it: MyTrimmedValue = TrimMe (my sell to trim)

VBA Code:
Function TrimMe(MyStr) As String
    If InStr(MyStr, "/") > 0 Then
        TrimMe = Mid(MyStr, InStr(MyStr, "/") + 1, InStr(InStr(MyStr, "/") + 1, MyStr, "/") - InStr(MyStr, "/") - 1)
    End If
End Function
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,054
Office Version
  1. 2016
Platform
  1. Windows
to use it: MyTrimmedValue = TrimMe (my sell to trim)
Sorry I diddi I don't fully understand what you mean, I am no good at InStr or Trims. I need this to work on column B sheet "Data"
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,304
Office Version
  1. 2010
Platform
  1. Windows
Range("C1") = TrimMe Range("B1")

C1 will have answer
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
Sub Sharid()
   Dim Cl As Range
   With Sheets("Data")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         Cl.Value = Split(Cl.Value, "/")(1)
      Next Cl
   End With
End Sub
 
Solution

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,054
Office Version
  1. 2016
Platform
  1. Windows
I have data in all columns, as the data is found it goes into each column, i was trying to clean. I was doing it with this

VBA Code:
Dim r As Long, Text As Long, m As Long
    Dim ws As Worksheet

    Set ws = Worksheets("Data")
    m = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    For r = 2 To m
        Text = InStr(1, Cells(r, 2), "?")
        Cells(r, 2).Value = Left(Cells(r, 2), Text - 2)
    Next

which game me this
<A class=_2yau href="about:/webdevlondon
<A class=_2yau href="about:/webdeveloperslondon
<A class=_2yau href="about:/UKWebDevelopers
<A class=_2yau href="about:/webdesignldn

Then I wanted to split it via the / and could not work it out.

However as always Fluff came to the rescue and all is well again.

I wish I knew what I was doing.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,135
Messages
5,640,318
Members
417,136
Latest member
reeton3

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
Top