excel 2002 convert numbers to mathematical

anon125

Active Member
Joined
Feb 14, 2008
Messages
343
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:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,178
Office Version
365
Platform
Windows
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?
 

anon125

Active Member
Joined
Feb 14, 2008
Messages
343
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:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,547
Office Version
2010
Platform
Windows
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),""))
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,442
Office Version
2010
Platform
Windows
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
 

anon125

Active Member
Joined
Feb 14, 2008
Messages
343
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:

anon125

Active Member
Joined
Feb 14, 2008
Messages
343
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,178
Office Version
365
Platform
Windows
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)
 

MARK858

Well-known Member
Joined
Nov 12, 2010
Messages
11,345
Office Version
365, 2010
Platform
Windows, Mobile
Thanks for trying Rick....
what did i miss?
PS the numbers were links at first
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-vba-xml-asp/using-the-immediate-window.html

Don't miss the previous post by Joe4
 
Last edited:

anon125

Active Member
Joined
Feb 14, 2008
Messages
343
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
 

Forum statistics

Threads
1,078,516
Messages
5,340,879
Members
399,396
Latest member
PBE

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top