How can I replicate Text to Columns from Excel in Qlik script?

[cliff_clayman]

I have an Excel file that I am bringing in to QlikView and creating a QVD.  Prior to using Qlik, I ran a macro on the Excel data to do Text to Columns on some of the columns.  I was able to successfully write in the script to use the Text() function on a few on the columns, but it is not working for one in particular.  The values in this column are mixed, meaning that there are some numbers and some text values.  When I run the script and put the field into a List Box, the number values have .000000 after them and I need to eliminate that. 

[m_woolf]

Can you provide a sample Excel wkbk?

[vishsaggi]

may be use below if not can you tell me what is your expected output?

Fabs(Num#(Yourfield))

[cliff_clayman]

The expected output with the provided screen shot would be....

60-89963-00

415

416

417

418

439

439N5900199739

[cliff_clayman]

I attached a sample of the Raw data to my reply below.

[m_woolf]

When I read from your sample wkbk, I don't see the issue you are describing.

This is my load script:

LOAD Reference,

     [Reference Key]

FROM

(ooxml, embedded labels, table is Data);

Here is a screenshot of the resulting data in a table box:

[cliff_clayman]

I got the same thing.  Not sure why it would be different in my main app.  It looks different here...

[m_woolf]

Open Settings|Document|Properties|Number.

Select the Reference field and set Number precision to 14.

[cliff_clayman]

The field needs to be Text as the values are not just numbers.  Also, I still get bad values when I make that change/

[m_woolf]

Those items pictured are numbers displayed in Scientific Notation formatting.