Please can anyone HELP!!? This is a tricky one to explain so please bare
with me. I have data that has been imported from a stock controll
program and my problem is this. In a column the data imported comes in
as billions (9 "0" zeros) after the first initial figure. So in other
words for example 13,000,000,000 The stock should read in thousands,
for instance 13,000. By highlighting the column and using the "Text To
Columns" delimited function and choosing "0" as the criteria I can get
the figure to read 13 and then using the format command and choosing
number to 3 decimal places with a thousand seperator I can then get
this figure to read correctly - 13,000 which is great BUT now the
problem - the figure underneath the 13 billion figure is 22,800,000,000
which should read 2,280 but now reads 228.000. How can I get this to
read correctly without going into each cell and do it individually, I
wanted to do the format as a highlighted column the data goes on for
thousands of rows so to do this each month on each individual cell is a
no no. Am I using the delimeter wrong? Can anyone help? I shall give you
the first five lines of data and what they should read
1) 13,000,000,000 should read 13,000
2) 2,275,000,000 should read 2,275
3) 22,800,000,000 should read 22,800
4) 5,292,000,000 should read 52,920
5) 1,008,000,000 should read 1,008


--
GKS
------------------------------------------------------------------------
GKS's Profile: http://www.excelforum.com/member.php...o&userid=32815
View this thread: http://www.excelforum.com/showthread...hreadid=526277

GKS

Rather than have a separate column and using Text to Columns ..., why not
have another column and just divide by 1,000,000.

Your explanation and examples are inconsistent but it looks as though you
just need to drop 6 zeros so dividing by one million would give you the
answer you need.

Regards

Trevor


"GKS" wrote in message
...

Please can anyone HELP!!? This is a tricky one to explain so please bare
with me. I have data that has been imported from a stock controll
program and my problem is this. In a column the data imported comes in
as billions (9 "0" zeros) after the first initial figure. So in other
words for example 13,000,000,000 The stock should read in thousands,
for instance 13,000. By highlighting the column and using the "Text To
Columns" delimited function and choosing "0" as the criteria I can get
the figure to read 13 and then using the format command and choosing
number to 3 decimal places with a thousand seperator I can then get
this figure to read correctly - 13,000 which is great BUT now the
problem - the figure underneath the 13 billion figure is 22,800,000,000
which should read 2,280 but now reads 228.000. How can I get this to
read correctly without going into each cell and do it individually, I
wanted to do the format as a highlighted column the data goes on for
thousands of rows so to do this each month on each individual cell is a
no no. Am I using the delimeter wrong? Can anyone help? I shall give you
the first five lines of data and what they should read
1) 13,000,000,000 should read 13,000
2) 2,275,000,000 should read 2,275
3) 22,800,000,000 should read 22,800
4) 5,292,000,000 should read 52,920
5) 1,008,000,000 should read 1,008


--
GKS
------------------------------------------------------------------------
GKS's Profile:
http://www.excelforum.com/member.php...o&userid=32815
View this thread: http://www.excelforum.com/showthread...hreadid=526277