General format not determining correctly the data type

**loslor** · 09-22-2006, 05:28 AM

Hi there,

I have an excel sheet in which I export data from a SSIS package. This data has several columns, mainly text and integers. My problem is that, despite the format I put into the cells, Excel is showing all the data as text, even the numeric columns. When I take a look into the format of those cells, they appear as 'General'.

This wouldn't be a serious problem (I can change the type manually) if it weren't because I'm supposed to generate these sheets and send them by mail automatically, and also i have a pivot table within the document that of course is not displaying the data correctly.

Any idea on why could be this happening?

Thx in advance

**Bryan Hessey** · 09-22-2006, 06:56 AM

Originally Posted by loslor

Hi there,

I have an excel sheet in which I export data from a SSIS package.

as import into Excel?

This data has several columns, mainly text and integers. My problem is that, despite the format I put into the cells, Excel is showing all the data as text,

Q. if you select one of these cells and look at the formula bar, is there a leading ' (tick) that does not reflect in the cell?

even the numeric columns. When I take a look into the format of those cells, they appear as 'General'.

This wouldn't be a serious problem (I can change the type manually) if it weren't because I'm supposed to generate these sheets and send them by mail automatically, and also i have a pivot table within the document that of course is not displaying the data correctly.

Any idea on why could be this happening?

Thx in advance

---

**loslor** · 09-22-2006, 07:52 AM

Hi Bryan,

The data is exported into excel (not imported) from an external program (Business intelligence studio). I've verified with several people that the data is exported correctly and in the adequate format, so that only leaves me with the excel.

Regarding your question, no, there is no apostrophe in the formula bar in any of the numeric cells.

I've been diving a bit through inet and read that this behavior could be something common when loading data from a database into the excel worksheet.

Any possible workaround for this?

Thx

**Bryan Hessey** · 09-22-2006, 08:24 AM

Originally Posted by loslor

Hi Bryan,

The data is exported into excel (not imported) from an external program (Business intelligence studio). I've verified with several people that the data is exported correctly and in the adequate format, so that only leaves me with the excel.

Regarding your question, no, there is no apostrophe in the formula bar in any of the numeric cells.

I've been diving a bit through inet and read that this behavior could be something common when loading data from a database into the excel worksheet.

Any possible workaround for this?

Thx

There are many articles on this, and I was trying to find some VBA code I spotted the other day to cure the sheet, but so far can only find a reference to setting the format (in VB) to
Selection.Numberformat = xlNumber

I will keep looking.
---

**loslor** · 09-25-2006, 09:35 AM

Thx a lot bryan.

**Bryan Hessey** · 09-25-2006, 09:47 AM

Originally Posted by loslor

Thx a lot bryan.

Relating to another post, if you do a =Code(A1) on your data fields, does the code 160 appear?

Found!
the following removes the 160 (no break space) character.

Sub TrimIt()
    On Error Resume Next
    Application.Calculation = xlCalculationManual
    Set Fn = Application.WorksheetFunction
    For Each cell In Selection.SpecialCells(xlConstants, xlTextValues)
        cell.Value = Fn.Substitute(cell.Value, Chr(160), Chr(32))
        cell.Value = Trim(cell.Value)
    Next
    Application.Calculation = xlCalculationAutomatic
End Sub

---

General format not determining correctly the data type

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

General format not determining correctly the data type

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions