Macro to create csv file delete leading zeroes

HI I have below code which have to make a csv file in Cell B1 separated by "Q, Q".. My problem is what ever i do, it delete the leading zeroes, and i need that to use this in other application.


Pasted in from A2

00002851
00011941
00014457
00014785
00015943
Result is like this in cell B1 Q2851Q, Q11941Q, Q14457Q, Q14785Q, Q15943Q So the leading zeroes is gone.

Please have a look in below code and see if some can help. Actually i need after to replace the all teh Q with ". But i cant do in the first run.

But the leading zeroes is the problem

Please have a look

Sincerely

Abjac

Please Login or Register  to view this content.

Hi Abjac,

Try the following which should keep your leading zeros.

Please Login or Register  to view this content.

Lewis

HI To all of you. I am really surprised about this. Dont know what is wrong. I upload the testfile now. I use excel 2013 now, dont know if that make a change. But i cant make this work. Please have a look at below test sheet. Both codes are inside.

Thanks in advance

Sincerely
Abjac

Hi abjac,

The problem seems to be not with the code, but with your data.

None of your data has any leading zeros. We can put in leading zeros with this data if you tell us how wide your fields are (they appear to be 8 digits).

Alternatively you can:
a. Format your cells as Text.
b. Put a leading apostrophe (') in front of the data, which will keep the leading zeros if there is any in the data (i.e. this treats the data as text.

Alternative Macro for 8 digits:

Please Login or Register  to view this content.

Lewis

Originally Posted by abjac

Please have a look at below test sheet. Both codes are inside.

Where are "leading zeroes" in your attached file ?‼

Sorry i am getting old. I try again with the leading zeroes in the column. LJMetzger. Your code give leading zeroes i just checked it.
But i can tell why i need the Q. In the application i have to paste this in, The separator has to be like this. ": ", but i cant put that into the code-
it would look like this then. "": "". That s why i replace the " with a Q and after i replace the Q with a ". SO when finish the separation will look like this. 00002851": "00011941.

Where in your code can i change this? And sorry now i will attach a sheet with the leading zeroes thanks.

Sincerely

Abjac

In the Macro you should be able to replace the "Q" with any character you want including ":" and ".". You may want to eliminate and/or modify one or more of the following to get your desired results:
a. The first "Q"
b. The last "Q"
c. The comma between items

Originally Posted by abjac

SO when finish the separation will look like this. 00002851": "00011941.

try

Please Login or Register  to view this content.

HI LJMetzger. I tried your code and if i put it together with a replace it give me the result i want. Jindon thanks allot. Your code works, but i need it displayed in exsample cell B1 so i can copy it after to another application.

But thanks allot really great help here.

Below is the code i landed on and its working for sure

Thanks

Have a nice day

Sincerely

Abjac

Please Login or Register  to view this content.

If that is the result that you want then

Please Login or Register  to view this content.

Thanks Jindon like normal your codes are brilliant. This one as well it works just perfect. Thanks allot.

Have a nice day

Sincerely

Abjac

You are welcome and thanks for the rep.