Imported Data, How to delimit correctly

[GKS]

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

[Trevor Shuttleworth]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>