@sir Alkey

Appreciated the formula. I have kept it if I plan to avoid a VBA code in future.

But currently I have large number of txt files and the vba solution is applied as it will also copy the data from txt files. Many thanks indeed.