Remove part of a sting in a cell

[jo_jan]

I would like to split the date next to my text in the cell below using a vba code, but the text will sometimes change and i only want the text in the first cell, not the date.
The first column of my table is what I have and the right column is what I want.


[TABLE="width: 500"]
<tbody>[TR]
[TD]JOHN (02/02/19)[/TD]
[TD]JOHN[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]02/02/19[/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

Will the date always be at the end? Do you want the end result in the first column or copied somewhere else?

 

[jo_jan]

The date I want is the one written next to JOHN. Thew result I want is only 2 columns with the text and the date under. I don't want to keep the first column.

 

[mumps]

Can you give us a few more examples of what you data looks like? For example, can there be more than 2 or 3 words in the cells? Is the date always at the end of the cell?

 

[jo_jan]

Can you give us a few more examples of what you data looks like? For example, can there be more than 2 or 3 words in the cells? Is the date always at the end of the cell?

No there is only one word before the date and there is always a space between them. And yes the date is always at the end of the cell! Thanks!

 

[mumps]

Try this macro. It assumes your data starts in row 2. If it starts in row 1, change the 2 (in red) to a 1.

Sub RemoveSubstring()
    Application.ScreenUpdating = False
    Dim LastRow As Long, x As Long, splitRng As Variant
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = LastRow To [COLOR="#FF0000"]2 [/COLOR]Step -1
        splitRng = Split(Cells(x, 1), " ")
        Rows(x + 1).Insert
        Cells(x + 1, 1) = splitRng(1)
        Cells(x, 1) = splitRng(0)
    Next x
    Application.ScreenUpdating = True
End Sub