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

[rickadams]

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:

Mrs. Melissa <8268513.cskm> Needs to just be Mrs. Melissa

<tbody>
</tbody>

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,>

 

[Joe4]

How about a formula like this (for an entry in cell A1):

=TRIM(LEFT(A1,FIND("<",A1)-1))

 

[jtakw]

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
	A	B	C
1	Mrs. Melissa <8268513.cskm>		Mrs. Melissa
Sheet26

Cell Formulas
Range	Formula
C1	=TRIM(REPLACE(A1,FIND("<",A1),255,""))

 

[Joe4]

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:

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

 

[Joe4]

And a few more VBA options:

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.

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!

 

[Rick Rothstein]

Here is one more VBA option...

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.

 

[Peter_SSs]

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.

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

 

[rickadams]

Here is one more VBA option...

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?

 

[rickadams]

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.

 

[Peter_SSs]

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

Range("F" & Rows.Count).End(xlUp).Offset(1).FormulaR1C1 = "=SUM(R1C:R[-1]C)"