Keep Zero infront of Pasted numbers, before convert to csv delimited list in Columm B

[abjac]

Hi I have a little problem,
I want to paste numbers in different format, some with zero in front, some without. Some with 2 zero, some with one.
All different numbers into columm A

In my program below i convert the pasted numbers into a csv delimited list in columm B.
I need this format converted, so i in another program can paste the csv delimited list into a dialog box for a search in a very big sheet with many rows.

My Problem is that it remove the zeros before the numbers, when i paste the numbers into columm A

I have been thinking of alot of solutions. But so far not found any.

Please have a look at the Test sheet i Attach. And use the text numbers i attach, to paste into columm A.

Hope some have a solution for this. And it should or have to be some automatic. Like in a macro.

p.s I use Excel 2003

Thanks in advance

Abjac

These are the numbers i use to test with, but they are also in the attached txt document.
0126057163
4114047
6014353267
1741888
1247451
1230088
001741888
AA09120094
3509120202
3B10120011
Q2PK428631
Q4EI735401
X0I309003D

[oeldere]

You don't put the data in the excel file.

Please do so, and also add the desired result.

[Grul]

If the cells contain numbers then 0126057163 is the same as 126057163 (Numerically)
If you want to keep leading zeros then set the format of column A as text before you paste.

[abjac]

Hi oeldere. The data to put in is in the attached txt file.
I want to paste these numbers in to columm A

0126057163
4114047
6014353267
1741888
1247451
1230088
001741888
AA09120094
3509120202
3B10120011
Q2PK428631
Q4EI735401
X0I309003D
And i want to keep the zeros infront. for those who have zeroes.
The result i want is the same, when i press the button to seperate them with , i still want the zero in front. So the resul is the same.

Grul. I have been thinking of the same. To set up columm A to text. Before i paste in(if that can be done)
And then have a macro to the button after, which make the numbers csv demileted, to columm B.
I dont know if this can be done, i have search alot, and can see many have this problem.

But if there is s solution, i would really like to know it.

Thanks for now both.
Have a loook.

Abjac

[oeldere]

select column A as mark it as text.

then paste special (text) in column A.

The data is with the leading 0.

I tested it and it works.

[abjac]

Hi I think i found the solution nearly the same as you guys say. I change columm A to text. I make so you can not save anything in the Workbook.
With these moduls.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

And in thisworkbook. This code.

Please Login or Register  to view this content.

So when i start my my Worksheet up, It will start with columm A set in text. And as you said oeldere its working.
I can make a savebutton if i want to save it.
And properly also a clear button, where i clear it and in the end put this code in to change columm A to text.

Columns("A:A").Select
Selection.NumberFormat = "@"
I think this will work for me.

Thanks for your help you lead me for sure in the right direction. So thanks alot
I will mark this as solved.

Abjac

[oeldere]

Glad i could help.

Thanks for the reply.