Excel Macro not working on other rows

[Riva80]

Hello,

This is my first time recording a Macro. This is what I am trying to do:

I have a list of data in a column that I want to modify for each row of that column. The data all have a similar format. For eg: 305969 FM233971 Mary 12/22/23 7:25 AM. I would like to remove the FM233971 Mary 12/22/23 7:25 AM part of this data for all cells and retaining just the (eg:)305969 part of it. I recorded the macro (code below) and I have onlty used the record mode since I dont code. I recorded this macro only for the first cell of that row in that column and then press tab to exit that cell (I have also tried enter to exit). Then stop recording the macro. Then I try running the macro for the next row cell in that column and it doesnt do a thing except just move the active cell selection to the tab /or wherever I exited while recording the macro. Unless I paste the contents of the row I want edited on the row I created the macro on, this doesnt work. 

Sub Macro1()
'
' Macro1 Macro
'

'
Range("A5").Select
ActiveCell.FormulaR1C1 = "305969"
Range("B5").Select
End Sub

[#2 · Accepted solution]

@Riva80 

The macro only refers to cells in row 5 (A5 and B5) and therefore can't change the output in other rows.

=LEFT(A5,SEARCH(" ",A5)-1)

An alternative could be this formula.

[#3]

Thank you, I will note this formula. I am trying to learn to Macro, so if you could point me in the way the Macro could be edited, that would be super helpful to me.

[#4]

@Riva80 

The following macro will remove the text from all cells in the column of the active cell:

Sub RemovePart()
    Const Text2Remove = " FM233971 Mary 12/22/23 7:25 AM"
    ActiveCell.EntireColumn.Replace What:=Text2Remove, Replacement:="", LookAt:=xlPart
End Sub

[#5]

@Riva80 

Sub Extract()

    Dim lastrowA As Long, f As String

    f = "=LEFT(A5,SEARCH("" "",A5)-1)"
       'Find a lastrow of column A
       lastrowA = Range("A" & Rows.Count).End(xlUp).Row
       Range("B5:B" & lastrowA).Formula = f

End Sub

You can run this macro which returns the result shown in the screenshot. One can't see that the formula was entered by a macro this time.