Saving file as txt with original delimiter ?

Hello,

This is my text test file : test file.txt

It looks like this originally :
screenshot test file.jpg

As you can see, the text file contains a delimiter (^). Sometimes this text file contains errors that we must fix manually. The solution to most of our problems with this file is to delete one or multiple lines and adjust the number of lines (D1^1 to 4 in the sample file) accordingly. So if we delete line 2 then 3 becomes 2 and 4 becomes 3. It wouldn't be so bad if the real files did not contain up to 1000 lines. We are currently editing everything manually. However, before editing the file manually, we open it in Excel because that makes it way easier to read, that way it is easier to see where the problem is.

Adding the txt file opened with excel : delimiter excel.xlsx

That is very clear. Very easy to read. The problem is that we can't edit the file in excel because it contains a delimiter, and when we save, it doesn't add it back.

This is the txt file after saving : saved txt.txt

And it looks like this :
screenshot saved file.jpg

This file as it is (when saved with excel) is unusable inside our data management tool. If the problems lies in line D1^50 and we must delete the line, and if there are a total of 800 lines, adjusting them all one by one so that the last line is D1^799 instead of D1^800 takes a very very long time.

I am wondering, is there anything that we can do in excel so that when we modify the file in excel, we can save it and it will appear like the original file ? It would be so much faster to simply drag down and autofill the numbers.

Thank you !

What the original file is supposed to look like : screenshot test file.jpg

What the spaces look like : emptyspace.jpg

When I look with notepad or anything else, it seems like that empty space counts as one character.

EDIT :

I found this code below and I modified it :

Please Login or Register  to view this content.

So in a cell, I am using :

Please Login or Register  to view this content.

Substitute because there are too many ^ on the first line and last line, and that I dragged the formula for the first 1000 lines.

Before marking this thread as solved, I will make sure to text it out then I will mark it as solved if there are no issues. I would still like to say thank you for your help ! If the function works however, the button may not be necessary.

Yes, I understand that. However, in the text file, it does add a wide space (similar someone pressing tab). I was able to select it/highlight it and it definitely counts as a character.

Updated workbook : ace test.xlsb

I did some adjustment to the file, so it does not do that and there are less modifications to be made.

However, this also causes another problem if someone can help me out with this :

The code that is used to save the sheet is save multiple empty lines under the last cell containing value. This is because I dragged down the formula for the first 1000 lines. I wonder if it is possible to change the code a bit to simply save the cells that contain a value and not the empty cells just because they have a formula in it.

The last row is always the one that starts with S1.

The code is inside the ACE module.

Please Login or Register  to view this content.

Thank you.