While it is on my mind I just though of 2 things you should do when you run this script the first time.
number1 open a new blank workbook and put this code into a module.


number2 try it on a folder with a few BACKED UP excel books in it.
Check the results and if everything looks good back up the intended folder and run it.

Should I save it as an Excel Macro-Enabled Workbook (.xlsm) file?

It worked really well on all the files in the folder, but something I noticed... Apparently, there are carriage returns in unexpected places. It leaves double commas (,,).
Here is a screenshot from the results after your code ran. It shows where the double commas occurred.

Possible Solution:
Perhaps, at the end of your code you can check for double commas “,,” and remove the extra one?
E.G: your code could check for double commas, finds something like this: "2,3,,8" remove the extra comma, and the result would be "2,3,8"

Attached Images

check-for-double-commas.png (21.1 KB, 18 views)

I think I did it correctly. I saved it as a Macro.xlsm, and ran it. For some reason the second folder, Column D didn't get properly changed. Weird.