exporting data to new excel adding " "

[kevinu]

I have some data that looks like this


Cer3R
Cer4,5L

when use a vba program to copy and paste it on a new excel sheet it comes out like this

1L - Cer3R
"1L - Cer4,5L"

any idea why it adds the " " when there is a , in there?

im using this to copy to new sheet

Please Login or Register  to view this content.

[Pepe Le Mokko]

Perhaps post a sheet?

[romperstomper]

Excel always does that when saving a workbook as a text file. You'll have to use an alternative method to create the text file.

[kevinu]

Here is an exmaple file. All i really care about is that the exported text does not have " " around entries with a , in them. Everything else is fine. We had a new update to our parent software and it now has some values with , in them.

[KOKOSEK]

Simplest thing is changing:

Cer4,5L into Cer4.5L

then no more "" around in TXT file.

[kevinu]

Thanks Kokosek, That does work, unfortunately the data comes to me with a comma and it requires a comma when i import my exported data back to it.

[ptmuldoon]

Can you change it from a comma to a period during the save to text? And then modify your input script to change it back?

Please Login or Register  to view this content.

[LJMetzger]

Expanding on KOKOSEK's suggestion:
a. Change the Comma to a pattern (e.g. 'qqq' or a character '~' that you never use)
b. When you import the text file, do a Universal replace of the pattern or character with a comma

For example:

Please Login or Register  to view this content.

Lewis

[kevinu]

I like the idea. I can get it to replace , with . then copy it to a new worksheet. Is there a way to replace the . with , after copying it before closing it out within the code above? I ask because I dont have access to change the parent program that imports it so i cant change it during import.

[LJMetzger]

Is there a way to replace the . with , after copying it before closing it out within the code above?

We were barking up the wrong tree. There is a simple solution without doing a replace.

Change:

Please Login or Register  to view this content.

Reference: https://docs.microsoft.com/en-us/off...orkbook.saveas

Thanks for the rep points.

Lewis

[kevinu]

Brilliant !!!!!! that did it.. thanks a lot