Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
KBear
 
Posts: n/a
Default Why are 1/2 my numbers imported as text and the rest as numbers?

I exported a file from a web based Lotus Notes database. When I open the
file in Excel 2003, 1/2 way down in the file, Excel converts the numbers to
text. If I open the file in Excel 2000, all numbers are imported as numbers.

I can use the multiply by 1 option to convert the text to numbers, but then
I lose some formatting.

If I only export a portion of the database, all the numbers are imported as
numbers.

An interesting side bar is that if I open Excel 2003 with a file that has
1/2 the numbers as text and the other 1/2 as numbers and then open a partial
extract where all the numbers are numbers, the partial extract will open with
all numbers as text. If I then close and re-open Excel 2003 and then open
the partial extract where all the numbers are numbers, Excel 2003 will open
the file with all the numbers as numbers again.

I'd like to determine a way for Excel 2003 to open the extract with all the
numbers importing as numbers.

Any help would be greatly appreciated.
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default Why are 1/2 my numbers imported as text and the rest as numbers?

I can't speak to the difference between Ex2k and Ex2k3, but I a couple
of things may be causing the numbers to import as text. Some database
programs concatenate an apostrophe before the entry, which Excel then
treats as text. Sometimes a leading or trailing blank space is
concatenated onto an entry during export, which excel also treats as
text. It's possible that neither of these instances would appear on
your screen, so you'd need to examine the cells by looking at them in
the formula bar to see if they exist.

If this is the case it suggests there's a problem with the export from
Lotus, rather than a problem with the import into Excel.

As a workaround you can insert a "helper" column, format it as a
number, and enter this formula for each imported value:
=VALUE(A1)
This will convert any text to a number.

  #3   Report Post  
Posted to microsoft.public.excel.misc
KBear
 
Posts: n/a
Default Why are 1/2 my numbers imported as text and the rest as numbers?

Thanks Dave,

Your solution works to convert the number to text, but when I copy down the
formula I still lose some formatting. Also, this extract that I am referring
to in my original question is availabe to in excess of 200 users within my
organization.

I still would like to no if there is a way for me to either fix Excel or the
download so that all users would need to do would be to open the file.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 08:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"