Microsoft Excel has a weakness in not 'playing nicely' when exporting to other applications, which can mess up your CiviCRM imports. Full instructions are given here on how to avoid this happening. The steps are simple: it essentially boils down to, 1) create a shape, 2) two clicks to create a macro, 3) copy and paste some code, 4) click on the shape.

Some ways Excel can make a mess of your import

Without these steps, a number of frustrating problems can appear:

To stop this happening, these steps show you how to make an Excel macro that you can use instead of 'Save as' to save a more conventional CSV file of the kind that CiviCRM expects. 

(for the techies: it creates a UTF-8 CSV file with quotes escaped, fields enclosed in double quotes, commas forced as a delimiter regardless of locale and a blank column at the end - but you don't need to know that!)

Creating the macro for beginners (Excel 2007)

1) Create a shape for the button. Go to 'Insert' tab, 'Shape', pick any shape, put it anywhere where you can click on it.
2) Assign a macro. Right click on the shape, 'Assign Macro', then hit the 'New' button.
3) Paste in the code. It'll come up with a code screen. Copy and paste the code below into the place where it puts the cursor (between 'Sub ...' and 'End Sub'). 
4) That's it! Close the code screen completely (you don't need to save), click off the shape to deselect it. Then click on the shape, and it should just work - popping up a 'save as' screen.

You can copy and paste this macro button into any other excel sheet you need to export: just right-click on the shape to select it, then copy and paste as normal. If you need to move it around, first right-click on it to select it without triggering the macro, then you can drag it about as normal.

It might not work on older versions of Excel. If the code gives an error, make sure you copied everything and no other non-code text, and make sure you're pasting the code before 'End Sub' and after 'Sub SomeShapeX_Click()' (for example it might be Sub SmileyFace3_Click() or Sub Rectangle1_Click() depending on the shape you chose).

The macro code to copy and paste:

Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant

FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
If FName <> False Then
ListSep = Chr(44)
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If

Set fsT = CreateObject("ADODB.Stream")
fsT.Type = 2
fsT.Charset = "utf-8"
fsT.Open

For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
If CurrCell.Value <> Empty Then
EscapedQuotes = Replace(CurrCell.Value, Chr(34), Chr(34) & Chr(34))
CurrTextStr = CurrTextStr & """" & EscapedQuotes & """" & ListSep
Else
CurrTextStr = CurrTextStr & ListSep
End If
Next
fsT.writetext CurrTextStr, 1
If (fsT.Position < fsT.EOS) Then
   fsT.SetEOS = fsT.Position
End If
Next
End If

fsT.SaveToFile FName, 2



From forum discussion http://forum.civicrm.org/index.php/topic,16442.0.html