Hi quake,

Instead of using a macro i advice you to use Powerquery and split by delimiter.
go to tab -> data -> get data (depends on where you data is stored either a range or a file) choose that setting than search the table and or file and than instead of loading do transform.
select the column that you wanto split and go to tab Start -> look up split by column -> click logo and you have multiple options to split text based on certain criteria.

greetings

Aaron