making a file asterisk delimited

[El Conquistador]

I work with EXCEL spreadsheets that are saved as .txt files so the vendor can work with them. In order for their program to work with the file, it needs to be asterisk delimited. The rows are data are of various length and there are times when a cell in the middle of a row is blank.

In the resulting EXCEL file, there are cells with multiple words, there are cells with nothing and some rows are 15 cells of text while others may have only three. There are cases where the last cell that correctly contains data is in the 8th column but there are empty cells in some of the previous columns.

I have resolved all of these problems by inserting an empty column between each existing column. Now, all I need to is be able to put an * where needed. This is based on the last column to have data, all of the inserted columns to the left need an *

[scott.s.fower]

wow took me a bit to figure it out, but i think this should work for you

Please Login or Register  to view this content.

[abousetta]

What is the delimiter in the txt file that you receive (e.g., comma, space, tab)?

The easiest way would be open the text file in memory, replace the delimiter with "*" and save the file again.

abousetta

[El Conquistador]

I need to make changes to the file that I receive and I am not sure that what you are suggesting would work.

[El Conquistador]

thanks for the assist. It seems to go belly up right at " If sht2v = "" Then" It says that there is a type mismatch.

[MrShorty]

Do you feel this has to be done in Excel/VBA? If I understand what you are doing, the easiest might be to open the text file in a text editor (like Notepad), do a Find/Replace, and save (kind of like abousetta suggested). From your sample, it looks like you need to find all the spaces and replace with * - an easy task for almost any text editor.

[El Conquistador]

It does need to be done in EXCEL, at the moment I am not using MACROS. Some of the data in the file needs to be adjusted and the easiest and fastest way to accomplish that is in EXCEL. If the file did not require editing, I would not load it into EXCEL in the first place.

It is not just a matter of inserting an asterisk in every blank space. There are blank spaces within the cells and your method would drop an asterisk in them.

[abousetta]

Here is what I mean.

Please Login or Register  to view this content.

abousetta

[El Conquistador]

sorry, but that does not help me at all.

Let me explain the entire process to you.
1. I receive a text file that is * delimited
2. If the file is a mess, I need to load it into EXCEL to fix it.
a. load it into EXCEL as an * delimited file.
b. correct problems
c. insert a blank column between eaxh existing column
d. insert an * in the blank cells (where needed)
3. Save file.

the difficult part is 2.d because not all of the rows have the same number of cells (ranges from 3 to 8). Some of the cells that would normally contain text are blank.

[MrShorty]

I think I'm understanding better. So the file is already asterisk delimited when you receive it (so there really is no need to replace spaces with *). The delimiter asterisk is "lost" when you load it into Excel, because you use the asterisk to indicate where the cell breaks are. The problem is -- how to get the asterisk back when saving the text file.

One thought. It is a global, Windows wide setting, but you can change the default delimiter for .csv text files (see the bottom of the this help page http://office.microsoft.com/en-us/ex...CTT=1#BMexport). If you set this to asterisk, then you should be able to simply save the spreadsheet as a "csv" file, and the Windows delimiter setting should be used as the delimiter (in this case * would be the delimiter). The main problem I see with this is if it will interfere with other csv files or other programs that use csv files.

If you can't change the csv delimiter setting, another thought might look like this:
1) load file and make necessary changes (there doesn't seem to be any problems in these steps).
2) save file as .csv (default comma delimiter or whatever your system is using) or tab delimited text.
3) Load text file into text editor, replace delimiter used in 2 with asterisk.
4) Save and close file.

[ussenterprise]

Additionally, Notepad++ has the ability to create macros. You could create a find/replace using the following:
Select Regular Expression
In the Find what: section, place the following without quotes: " *" (Thats two spaces and the *)
In the Replace with: section, place the following: *

(I just tested this, and it works wonderfully!)

[El Conquistador]

I am sorry but you do not understand the complexity of my situation. I have spent hours trying to work this problem out and the only solution that is going to work in my case is as I described using EXCEL and MACROS.

Your proposed solution does not take into consideration at the lines of text are of varying length. Some have three delimited entries, some have fourteen.

[ussenterprise]

Hi El Conquistador.
My solution turns every space into an asterick. (It looked really funny in my text file...)
It doesn't look at lengths, only spaces. I have used this solution to turn spaces into {tabs} for use in importing into excel in the past.

Are there other text that would not be * delimited? If so, then this solution wouldn't work unless you have common text that can be excluded...

[ussenterprise]

Example of before:

Please Login or Register  to view this content.

And after:

Please Login or Register  to view this content.

(All of my macros are saved as text files...)

[El Conquistador]

unortunately, I have lots of spaces that should not be *.

[abousetta]

Let's see if this suggestion works and then we can work on automating it a bit further:

1. You receive a text file that is * delimited
2. If the file is a mess, you:
a. Open the text file in EXCEL, * delimited file.
b. Correct problems (This step is highly unlikely to be automated unless there is a pattern to the messiness and therefore a patter to how to rectify the problem. It seem that this is a random error.)
c. Insert a blank column between each existing column
d. Save file, tab delimited.
3. Run the macro posted above (post #8) - after changing the name and path of the text file.

This should do exactly what you are looking for.

abousetta

[El Conquistador]

When I open the file in EXCEL as an asterisk delimited file, the asterisks do not transfer (obviously). What I need to create (after the corrections are made is not a tab delimited file but an asterisk delimited file.