If you Know excel....?

Question

When making a copy/paste from a website table into excel, the numbers are not being recognized as such (each number is within one cell), and I can not make any calculations or functions with them!!!

Help Please

Note: Format the cell as number does not work

Answer 1

Use the Value function. If the 'number' you pasted is in cell B3, then in an adjacent cell, enter the formula
=VALUE(B3)

That will evaluate the text as a number. Simply formatting the cell containing the text won't necessarily fix the problem, since Excel allows you to enter text in a cell formatted for numbers.

Answer 2

If you are trying to get data from a website table, try this method. Most of the time, it works for me. The only times I can remember that it doesn't work is if the table is a graphic.

Copy the website address that you found the data from, go to Excel, click Data on the menu toolbar, select "Import External Data", and select "New Web Query". When a new window appear, paste the website address onto the adress bar and press [Enter] or the arrow.
All the informations on that site will appear, but once all the informations/data are loaded, there will be an arrow on the top left corner of the table. Click on that arrow and the table should be hi-lited blue. Press the Import button on the bottom of the screen, which then you will be directed back to Excel. A dialogue box will appear asking where do you want to put that data. Click on the area where you want it to be at (for example... if i wanted the data to start on A25, I would click on A25). Press OK and the data table will appear on excel. after that, you can manipulate the table to your liking, or in your case, add the numbers

Answer 3

The short answer: Click F2 and then Enter for each cell.
Assuming your column is only a couple dozen of cells get yourself into a semi-OCD mode and click F2 > Enter > F2 > Enter > F2 > Enter > F2 > Enter > etc.
If you have any programming skills you can do it with VBA. If you have none, use MacroExpress to automate the task (www.macros.com).

The long answer: If you need to import data off that website on a regular basis, you should consider other ways of importing it (e.g. Saving it to data.txt and then opening it in Excel, choosing the meaning of each column).

P.S.
For all the people that keep mentioning Format Cells - it has NOTHING to do with that, as it only changes the way the cell _looks_ and not the way Excel interprets its contents.

Answer 4

Right click and choose paste special and HTML. If that does't work you can try text or paste special and then values.

EDIT - Maybe I was not specific enough. Left click only once in the cell into which you would like to paste the table so that it is selected, but only once so you are not in edit mode. Then, right click with your mouse. You will see a menu of options appear. Choose the "Paste Special" option. Choose HTML. It should work. If not, then the Text or Unicode text will work. I just tried this and it worked. I am Microsoft certified in Excel. Don't listen to anyone who has to tell you to do it one by one. Excel is all about using shortcuts for efficiency.

Answer 5

You need to format in Format, Cell. This way the cell recognizes what you are wanting. Good luck

Answer 6

On your selected data try: Right Clicking - Format Cells - Then on the General Tab make sure you choose NUMBERS.

Answer 7

You need to format the cells and tell them they are numbers.
Select cells.
Format > Cell > Number

Answer 8

Select the cells with numbers and change their format to a numerical one they are probably be seen as text.

Answer 9

Check the formatting of the cell and make sure you have it in the correct format.

Answer 10

Of course, it just doesn't work, they're different formats. Forget it.You got to enter the stuff manually into each excel cell. That's the way the cookie crumbles.