convert excel list to pivot table

[Guest]

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.

 

[Bernie Deitrick]

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

 

[Guest]

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

 

[Dave Peterson]

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.0000"))

=====
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.

 

[Guest]

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#####.

 

[Bernie Deitrick]

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

 

[Guest]

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