Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: excel 2002 convert numbers to mathematical
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2008
    Posts
    329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default excel 2002 convert numbers to mathematical

    i copied numbers from an online account.
    pasted them into excel 2002.
    how do i convert the numbers to numbers i can multiply etc?
    i tried putting a 1 into a cell then copying and paste special multiply

    tried converting the cell to numbers

    thanks all
    Last edited by anon125; Oct 16th, 2019 at 09:01 PM.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,032
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: excel 2002 convert numbers to mathematical

    Try doing a "Text to Columns" on the column, i.e. highlight the whole column, select "Text to Columns" from the Data menu, and click Finish.
    Does that fix them?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Feb 2008
    Posts
    329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 2002 convert numbers to mathematical

    tried =a1+0
    the following does not make sense. i have no rules section

    • To do this in Microsoft Excel 2002 or in Microsoft Office Excel 2003, follow these steps:
      • On the Tools menu, click Options.
      • In the Options dialog box, click the Error Checking tab.
      • In the Settings section, click to select the Enable background error checking check box.
      • In the Rules section, make sure the Number stored as text rule is selected, and then click OK.

      To do this in Microsoft Office Excel 2007, follow these steps:
      • Click the Microsoft Office Button, and then click Excel Options.
      • On the Formulas tab, click to select the Enable background error checking check box in the Error Checking section.
      • Under Error checking rules, make sure that the Numbers formatted as text or preceded by an apostrphe check box is selected, and then click OK.

    • On the Error Checking Options button, click the down arrow.

      Notice that the Number Stored as Text menu appears.
    • Click Convert to Number.

    Notice that the cell is converted to the number format and the Error Indicator no longer appears in the cell.
    Last edited by anon125; Oct 16th, 2019 at 09:24 PM.

  4. #4
    Board Regular
    Join Date
    Mar 2014
    Posts
    2,484
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: excel 2002 convert numbers to mathematical

    It would help (us) if you uploaded an example Excel file to a file-sharing website and posted the download URL here.

    My guess: the cells contain non-breaking spaces (HTML nbsp; ASCII 160). If so, try the following:

    =--SUBSTITUTE(A1,CHAR(160),"")

    The double negate converts text to a numeric value. Any idempotent arithmetic will do the same; for example, adding zero or multiplying by one.

    Just in case the cells might contain other "invisible" characters (notably tab; ASCII 9), you might try the following:

    =--CLEAN(SUBSTITUTE(A1,CHAR(160),""))

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,315
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: excel 2002 convert numbers to mathematical

    Are your "numbers" left justified? If so, they are text values. In order for that to be the case, I suspect you have some trailing non-breaking space attached to the numbers. Give this a try...

    1) Select the column with your "numbers" in it.

    2) Go into the VBA editor (ALT+F11) and type this into the Immediate Window and then press the Enter Key...

    Selection.Replace Chr(160), "", xlPart, , , , False, False
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #6
    Board Regular
    Join Date
    Feb 2008
    Posts
    329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 2002 convert numbers to mathematical

    Thanks for trying Rick, but when i press Alt+F11 i just get a grey screen in the VBA editor. nowhere to type!
    so i added a module and typed it there but to no avail.
    what did i miss?
    PS the numbers were links at first
    Last edited by anon125; Oct 16th, 2019 at 11:03 PM.

  7. #7
    Board Regular
    Join Date
    Feb 2008
    Posts
    329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 2002 convert numbers to mathematical

    Quote Originally Posted by Joe4 View Post
    Try doing a "Text to Columns" on the column, i.e. highlight the whole column, select "Text to Columns" from the Data menu, and click Finish.
    Does that fix them?
    no but thanks

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,032
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: excel 2002 convert numbers to mathematical

    Can you post an example of any number on your sheet, what it looks like?
    Then, for that exact value, let's say it is in cell A1.
    What does this formula return?
    =LEN(A1)
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,230
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: excel 2002 convert numbers to mathematical

    Quote Originally Posted by anon125 View Post
    Thanks for trying Rick....
    what did i miss?
    PS the numbers were links at first
    Quote Originally Posted by Rick Rothstein View Post
    and type this into the Immediate Window and then press the Enter Key...

    Selection.Replace Chr(160), "", xlPart, , , , False, False
    I think you missed the Immediate Window part, if you can't see it in the VBE click the View icon then click Immediate Window, it normally appears at the bottom of the window where you type your code. Please note that you might need to drag the top of it to expand it.
    See the link below to see how it appears.

    https://www.engram9.info/excel-2002-...te-window.html

    Don't miss the previous post by Joe4
    Last edited by MARK858; Oct 17th, 2019 at 12:09 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  10. #10
    Board Regular
    Join Date
    Feb 2008
    Posts
    329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 2002 convert numbers to mathematical

    Quote Originally Posted by Joe4 View Post
    Can you post an example of any number on your sheet, what it looks like?
    Then, for that exact value, let's say it is in cell A1.
    What does this formula return?
    =LEN(A1)
    Thanks
    the length is indeed correct.
    the numbers look like any other number. position is of course affected by the alignment buttons

    thanks

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •