Macro to separate long string of characters and several spaces into separate columns

I am looking basically to do a MID formula, but the value of the cell is like "A04AG605S206704561 BANNER" and I need the characters after the S (9 characters in) and then I need the name (Banner) I need these separated into columns. They will always start in A1, but not every row will have this data, Some may be "D123445678123". So I don't know if that is a front end thing, to only apply if the cell starts with A, or on the back end and after everything is separated into three columns, remove anything that doesn't have a value in column 3. This may also go 500 rows down, or 200 rows. Changes every day.

Would it be best to first do a text to column type of thing and separate on the first space, then a mid type formula, but in a macro fashion. With 500+ rows, I am worried that it may use to much resources and because a different user will have the spreadsheet other than I and I can set it as a button versus afraid the formula gets adjusted.

I have attached the spreadsheet which should be clearer than my explanation. Thanks!


*edit, new workbook now without password!

Hmmm... 2nd time in 2 days that someone posts a password protected file...

SpecialCells method may have issue with big data but like here it is not the case :

PHP Code: 

Sub Macro1()
        Application.ScreenUpdating = False
    With Cells(1).CurrentRegion.Columns
        If Application.CountBlank(.Item(2)) Then .Item(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .Item(2).Cut Cells(3)
        .Item(1).TextToColumns , xlFixedWidth, FieldInfo:=Array([{0,9}], [{9,2}], [{18,9}], [{32,2}])
    End With
        Application.ScreenUpdating = True
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !