×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel number not a number

Excel number not a number

Excel number not a number

(OP)
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.  :(

RE: Excel number not a number

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.

RE: Excel number not a number

Another handy tool is Asap Utilities, a free Excel plug-in from 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!

RE: Excel number not a number

(OP)
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.

RE: Excel number not a number

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!

RE: Excel number not a number

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

RE: Excel number not a number

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

RE: Excel number not a number

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

RE: Excel number not a number

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources