Excel number not a number

[RonShap]

I have the export numerical data from a calculation program, which outputs an excel format. When I open the file, it has lots of formating that I am not interested in, so I copy the cells I need, then paste special - values, into a new excel file. When doing a formula, it doesn't recognize the information in the cell as a number. The formula works as if there is no information in the referenced cell. If I retype the numbers, then the formula works. The retyped number appears on the right side of the cell, whereas the information before it is retyped shows on the left side of the cell. The format of the cell is General. When the format of the cell is changed with the original information in it, to number, the formula still doesn't work.
Is there an automated way of retyping this numbers, as the files of data is long.
I though the paste special - values, would do it, but it doesn't.

 

[analogkid2digitalman]

Sounds like it may have came into Excel as text. Do the 'numbers' have a ' in front of them, i.e. displays '123 in the editing box? This would identify it as text. Have you tried to renaming the original file as *.dat or *.txt and then do a File/Open in Excel to start the Text Import Wizard? You may be able to properly parse the data without having to try Copy/Paste.

Wheels within wheels / In a spiral array
A pattern so grand / And complex
Time after time / We lose sight of the way
Our causes can't see / Their effects.

 

[jhardy1]

Another handy tool is Asap Utilities, a free Excel plug-in from

http://www.asaputilities.com.

This plug-in offers you heaps of really useful new tools, one of which will delete all non-numeric characters in a selected range, leaving you with just the equivalent numeric data. For example, if a range of cells contains the following text strings:

'Data Point 1 Some More Text'
'Data Point 2 Some More Text'
'Data Point 3 Some More Text'

and so on, using this tool will immediately convert the contents of the selected cells to the following numeric data with a single click:

1
2
3

Everyone should download and install this great set of tools for Excel!

 

[RonShap]

The information doesn't appear to be text. The formula I was using (max) would alphabetize text, although the result was 0

The work around I have done, is have the calculation program export the information as a .htm file, then I open it in excel. Seems to work then.

 

[onlyadrafter]

Hello,

This may work.

In a cell enter 1, select this cell and select EDIT --> COPY, select the cells with the data, select PASTE SPECIAL and check the MULTIPLY check box.



----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[pmover]

RonShap,

while there may not be a tick mark, there may be a space or spaces before or after the number. try using the value function, which converts the text to a number. substitute the formula with value(address) and see if that corrects the problem.

good luck!
-pmover

 

[aviat]

If the number is on the left it usually means that Excel is reading it as text. The following website says to do it as onlyadrafter said.

http://www.bellaonline.com/articles/art10036.asp

 

[bltseattle]

I think there is a TRIM function that can also be used to clean up extra spaces....

 

[RonShap]

As a followup (sorry so late), onlyadrafter's suggestion worked. Multiplied the "number" by 1 and it cleared up the problem.

 

[dood51]

RonShap,
If the data is being written to excel in the form of a 'string', then you need to convert it to a number. If you're using a more recent version of excel you'll see a colored corner on each of the cells. If that's the case, try right-clicking on the cell(s) and choose 'convert to number'. If you're using an older version, highlight the cells you want to convert and go to the excel 'functions'. Under the "text" category, go to "value" and click OK. This will convert the text string that represents a number to a number. Hope that helps.
Dood51