I have cells with a varying number of account numbers separated by
semi-colons that I need to extract and put each into their own cell. The text
to columns works as far as extracting each account number into separate cells
to the right of the original but I need each account number to be as follows:

Current row format:

Customer Account Number Customer Number
David Vollmer 123456789; 246813579; 852741369
987654321


Desired outcome:

Customer Account Number Customer Number
David Vollmer 852741369
123456789 852741369
246813579 852741369
987654321 852741369

This data will be imported into an Access table with the first row of data
above being input into one table and the other rows imported into an account
number table.

I would like to do this with a macro either automatically or
semi-automatically. Your help is most appreciated.