How do I format for Long Numbers in EXCEL ?

  Furkin 12:06 17 Feb 2008

When i'm doing stuff on e-bay, I sometimes do a temp chart of the stuff i'm buying &/or selling, in Excel.
In the first column I put the item number (in this case the other columns don't matter)
If I cut'n'paste the number from e-bay, it dosn't come out quite right, until I format it as a single large number with no decimal places.
So far - so good !
The trouble is, that I can't format the whole column to do it ! In all other columns, & highlight as much column as I want & format it accordingly.
For eg: 200188832082 comes out as: 2.00189E+11.

Not life threatening,,, but another niggle,,,,

any ideas please ?

  Noldi 12:14 17 Feb 2008

You could use paste special > Text


  VoG II 14:23 17 Feb 2008

Format the column as Text BEFORE pasting.

  Furkin 20:37 17 Feb 2008

sorry guys,,,, neither seems to work,,,,,

  VoG II 20:39 17 Feb 2008

Having copied a value

Click in a cell

Type '

Press CTRL + V

  VoG II 20:48 17 Feb 2008

Right click the sheet tab and select View Code. Copy and paste in

Private Sub WorkSheet_Change(ByVal Target As Range)
If Target.Column = 1 Then Target.Value = "'" & Target.Value
End Sub

Change the 1 to the column number you are interested in (1=A, 2=B, 3=C etc.). Then close the code window.

  Furkin 13:00 18 Feb 2008

Thanks VoG, The ' does the trick,,,, well for single entries anyway.
This will suit my purpose thanks.
For future reference - what does the ' mean in this case ?

Will click resolved

  bstb3 14:05 18 Feb 2008

the ' when entered as the first character in a cell forces Excel to display and treat all the following characters as text, whilst not displaying the '. It is very handy when Excel auto-formats things, such as dates.

For example entering November 2007 is often auto-formatted to Nov-07. Entering 'November 2007 is not auto-formatted and is displayed as November 2007.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?