I'm working with a program that exports data to a text tab delimited file. When I need to edit large sections of data it is easier to export the database to the text file and open it in Excel to manipulate the data.
The problem is with the column of data that contains number addresses. The program uses the format #,#####. As soon as I open the file in Excel it sees this column data as a Number, and inserts commas since the "Use 1,000 separator" option is selected. When the file is imported back into the program this causes errors since the number before and the numbers after the comma have specific meanings.
My workaround is to convert the column to text, and then manually insert the required comma after the first digit in each cell. Doing this one by one takes a long time. I then save the file and import it back into the program. This works, but if I go and open the file in Excel again, I lose all the formatting I just did.
For example:
Address in program is listed as: 1,23456
When the export file is opened in Excel, the address is automatically changed to: 123,456
I don't think there's a way to prevent Excel from changing the data when the file is opened, I'm just looking for a quick way to correct the data instead of manually correcting each cell one by one.
I've been looking in the Excel help files for a function that can insert a character (comma in this instance) after a specified position in a text string (after the first character in this instance), but I haven't been able to find one. Maybe this is better solved with a macro?
Any help is appreciated.
Thanks
Bookmarks