Need to delete characters in a column

[KAZSTREBOR]

Hi there!

I need to delete characters generated from a report that come after the space. (eg. 1234 cds), the characters vary so I can't target specific ones.
I'm currently trying to use this code in VBA that I modified from this thread but I can't figure out why it is also deleting the first character in the cell. I'm pretty new to this so any
help is much appreciated. Thanks!

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    On Error Resume Next
    For Each rng In Range("B1:B" & LastRow)
        rng = Left(Mid(rng, 2, 9999), WorksheetFunction.Find(" ", rng) - 2)
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Kazstrebor()
   With Range("B1", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("left(@,find("" "",@&"" ""))", "@", .Address))
   End With
End Sub

 

[mikerickson]

Perhaps TextToColumns.

With Range("B:B")
    Range(.Cells(1, 1), .Cells(Rows.Count).End(xlUp)).TextToColumns Destination:=.Cells(1,1), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=False, _
        Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 9), Array(4, 9), Array(5, 9), Array(10, 9)), TrailingMinusNumbers:=True
End With