Remember Me?

#1
December 6th 04, 06:45 PM
 GI Posts: n/a
convert excel list to pivot table

Thank you very much.. sorry , it took a while for me to reply. But, it works
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

 Thread Tools Search this Thread Search this Thread: Advanced Search Display Modes Linear Mode

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Mike -Z- Charts and Charting in Excel 1 December 7th 04 09:39 PM souris Charts and Charting in Excel 2 December 7th 04 03:56 AM Santhosh Excel Discussion (Misc queries) 1 December 2nd 04 10:15 AM Mansel Excel Discussion (Misc queries) 1 December 2nd 04 12:55 AM Big Red Excel Discussion (Misc queries) 1 November 29th 04 08:42 PM

All times are GMT +1. The time now is 10:00 PM.