Format data copied from other app

  • Thread starter Thread starter Alan DeHaven
  • Start date Start date
A

Alan DeHaven

Sometimes when I copy info from another app like MS Access, some columns
don't seem to retain their format. When I change the format for the column
nothing really seems to happen until I hit F2 and enter, then I get the
format I'm looking for. That's fine except I have a thousand records. Is
there anyway to do this faster?
 
Instead of pasting the data from Access, you can choose Edit>Paste
Special, and choose csv or Text. Or, do a normal paste, and then change
the data back to numbers:

1. Select an empty cell on the worksheet
2. Choose Edit>Copy
3. Select the cells that you pasted from Access
4. Choose Edit>Paste Special
5. Select Add, click OK

If you do this frequently, you can use a macro to paste as csv:
'=======================
Sub PasteCSV()
ActiveSheet.PasteSpecial Format:="Csv", _
Link:=False, DisplayAsIcon:=False
End Sub
'========================

Or to convert the numbers:
'=============================
Sub ConvertToNumbers()
'by Jon Peltier
Cells(65535, 255).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
End Sub
'=============================
 
Back
Top