Thank you very much.. sorry , it took a while for me to reply. But, it works
with your macro.
I was wondering if you can help me with another problem that I have.
I created those pivot table with the part numbers as the primary column and
and quantity as the second column by summing the quanitty.
Then I created independent column next to it for my own purposes, but when I
refresh the pivot table, it won't refresh those independent column.
Do you know any tricks for it?
Thx

"Bernie Deitrick" wrote:

GI,

How about a macro: Select all the cells and run this

Sub ConvertToText()
Dim myCell As Range

For Each myCell In Selection
myCell.NumberFormat = "@"
myCell.Value = "'" & myCell.Value
Next myCell

End Sub

HTH,
Bernie
MS Excel MVP

"GI" wrote in message
...
Thanks for the help.
those numbers represent part numbers. There are too many mix type
numbers.
After the dot, there are 4 digits and 5 digits. I tried with your
formula,
it works but for pn such as 012345-001, it did not work and also between 4
and 5 digits.
Those numbers are not from external source. It was entered before and
provided it to me. And those numbers are formatted in cells with custom
number format 0#####.

"Dave Peterson" wrote:

I think the next question is how do you know how many places to keep
after the
"decimal" point.

012345.2310 looks like it could have been 012345.231

If you always have a dot in the part number, is it always followed by 4
digits?

=IF(MOD(A1,1)=0,TEXT(A1,"00000"),TEXT(A1,"00000.00 00"))

=====
If you got this list from an external source (a text file???), it might
be
easier to reimport it into excel--but specify Text for that field.



GI wrote:

Thanks for the help.
Is there a way to make the job simpler... Since I have 2000 numbers to
revise then.
I tried using =text(a1,"000000") but this one doesn't capture the part
number 012345.2310
--GI

"Bernie Deitrick" wrote:

GI,

Format the cells as text prior to entering the numbers, or use a
single
quote in front of the number string.

HTH,
Bernie
MS Excel MVP

"GI" wrote in message
...
Hi.
I've been trying to convert a list of mix numbers such as 010456,
123456,
010686-001, 010686-002, 015678-AB and much more to a pivot table.
Everytime
I created the pivot table using those numbers as pivot, EX: 010456
automatically changed to 10456, it doesn't recognize as 6 digits.
I tried
using text, custom 0#####, still can't get it sort right.
Does anybody know how to solve it. Thanks a lot.





--

Dave Peterson