Delete text in row or column after < and delete the < too

rickadams

New Member
Joined
Feb 11, 2018
Messages
32
I am looking for a VBA to delete All the text after the < and the < too
My Sheet has Columns A Thru J and can be anywhere from 2 to 200 rows

IE:

[TABLE="class: cms_table, width: 226"]
<tbody>[TR]
[TD="width: 226"]Mrs. Melissa <8268513.cskm>
Needs to just be
Mrs. Melissa[/TD]
[/TR]
</tbody>[/TABLE]

So I need to delete everything after the < and the < too.

<anytown, nj="" 02220="">Everything I have tried in VBA gives me errors.

Thank you</anytown,>
 
Last edited:

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
How about a formula like this (for an entry in cell A1):
Code:
=TRIM(LEFT(A1,FIND("<",A1)-1))
 
Upvote 0
Hi,

I came up with this, but didn't post it since OP asked for a VBA solution, but I guess I'll just throw it in:


Book1
ABC
1Mrs. Melissa <8268513.cskm>Mrs. Melissa
Sheet26
Cell Formulas
RangeFormula
C1=TRIM(REPLACE(A1,FIND("<",A1),255,""))
 
Upvote 0
I am not sure if they asked for VBA because they didn't know if it was possible to do with just formulas, but if they really want a VBA solution, here is one:
Code:
Sub MyReplace()

    Dim lrow As Long
    Dim cell As Range
        
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lrow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Replace all values in column A
    For Each cell In Range("A1:A" & lrow)
        cell = Trim(Left(cell, InStr(cell, "<") - 1))
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
And a few more VBA options:
Code:
Sub MyReplace2()

    Dim lrow As Long
    Dim cell As Range
    Dim LArray() As String
        
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lrow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Replace all values in column A
    For Each cell In Range("A1:A" & lrow)
        LArray = Split(cell, "<")
        cell = Trim(LArray(0))
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub
or use Text to Columns, i.e.
Code:
Sub MyReplace3()

    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="<", FieldInfo:=Array(Array(1, 2), Array(2, 9)), TrailingMinusNumbers:=True
        
End Sub
Note that the last one may have trailing spaces on the end if there is one before the "<", though you could add code to clean those up to.

And you could just manually use Text to Columns for a non-VBA and non-formula solution.

As you can see, if Excel, there are often many different of accomplishing the same task!
 
Upvote 0
Here is one more VBA option...
Code:
Sub ReplaceLessThanAndFollowingText()
  Columns("A").Replace "<*", "", xlPart, , , , False, False
End Sub
And, of course, you could to this manually by selecting Column A, pressing CTRL+H to bring up the Replace dialog box, typing <* in the "Find what" field, leaving the "Replace with" field blank, clicking the "Options>>" button and making sure the "Match entire cell contents" check box is not checked, clearing any formats that may be set and then clicking the "Replace All" button.
 
Last edited:
Upvote 0
I was on the same path as Rick (& included all columns) & if the 'trim' part is, or may be, required perhaps the second of these.
Code:
Sub RemoveText1()
  Columns("A:J").Replace What:="<*", Replacement:="", LookAt:=xlPart
End Sub


Sub RemoveText2()
  Columns("A:J").Replace What:=" <*", Replacement:="", LookAt:=xlPart
  Columns("A:J").Replace What:="<*", Replacement:="", LookAt:=xlPart
End Sub
 
Upvote 0
Here is one more VBA option...
Code:
Sub ReplaceLessThanAndFollowingText()
  Columns("A").Replace "<*", "", xlPart, , , , False, False
End Sub
And, of course, you could to this manually by selecting Column A, pressing CTRL+H to bring up the Replace dialog box, typing <* in the "Find what" field, leaving the "Replace with" field blank, clicking the "Options>>" button and making sure the "Match entire cell contents" check box is not checked, clearing any formats that may be set and then clicking the "Replace All" button.


Rick, You are the MAN! THANK YOU! This code is Neat And Clean And you make this seem so easy. Thank you again...

If I need to Autosum dollar amounts in column F in this sheet which may be 2 rows up to 100 or more rows, How easy is that to add to this?
 
Upvote 0
Peter, Thank you for your suggestion.
I knew there was a way to check the rest of the rows and columns in this sheet for the same <. I forgot how.
 
Upvote 0
Peter, Thank you for your suggestion.
I knew there was a way to check the rest of the rows and columns in this sheet for the same <. I forgot how.
You are welcome. :)


If I need to Autosum dollar amounts in column F in this sheet which may be 2 rows up to 100 or more rows, How easy is that to add to this?
Try
Code:
Range("F" & Rows.Count).End(xlUp).Offset(1).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

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