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

[taylorsm]

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!