View Single Post
  #1   Report Post  
Old December 6th 04, 06:45 PM
Posts: n/a
Default convert excel list to pivot table

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?

"Bernie Deitrick" wrote:


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

MS Excel MVP

"GI" wrote in message
Thanks for the help.
those numbers represent part numbers. There are too many mix type

After the dot, there are 4 digits and 5 digits. I tried with your

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


=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

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

"Bernie Deitrick" wrote:


Format the cells as text prior to entering the numbers, or use a

quote in front of the number string.

MS Excel MVP

"GI" wrote in message
I've been trying to convert a list of mix numbers such as 010456,

010686-001, 010686-002, 015678-AB and much more to a pivot table.
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