Excel not seeing numbers as numbers??? 7

[JBruyet]

Hey all,

I have a supervisor who is getting some web-based reports for some statistics. I'm trying to get some totals from the reports but formulas don't see the cells as numbers. If I have a formula like =SUM(G7:G26) I get zero as the answer, even though each cell has a number in it. AND, I don't get the little green corner that says it's a number formatted as text. I tried formatting the numbers in the cells as General, Number and General but no formulas see the numbers as numbers. I've even tried the following:

1) Copy clean blank cell and then go back to the Sheet containing your imported data.
2) Push F5 (to display the Go to dialog) and click Special then check Constants and click OK.
3) While all your data is selected go to Edit>Paste special then check the Multiply or Add option and click OK.

That just leaves me blank cells where I wanted to paste data. Any ideas???

Thanks,

Joe Brouillette

 

[Lilliabeth]

Step 1 should read: Put the number 1 in a cell and copy it.

 

[Lilliabeth]

Oh - and choose the multiply, not the add option

 

[xlbo]

Whenever you paste data in from another application, it tends to come in as text - if it is pasted from an MS app, it will tend to have the "little green tags" - if not, it won't. As Lilliabeth has mentioned, the steps are as follows:

1: Enter a 1 into any blank cell
2: Copy it
3: Select the range of "textual numbers"
4: Edit>Paste Special choose "Values" & "Multiply"

et voila

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JBruyet]

Ok, here's what I'm doing:
1) I put a 1 in a cell and press CTRL-C
2) I press the F5 key and click on the Special... button
3) I click on the Constants button
4) I click on OK
5) I click and drag to select the numbers
6) I click on Edit
7) I click on Paste Special
8) I click on the Multiply button
9) I click on OK
10)I get a zero in my destination cell

I see I didn't write that I'm using Excel 2003. Would that make a difference? I've tried with and without pressing the F5 key. I've tried some other random variations of the instructions but I always get a zero in my destination cell.

Thanks for the help,

Joe Brouillette

 

[Lilliabeth]

1. Put a 1 in a cell and make sure that cell is NOT text.
2. Copy the 1
3. Drag to select the "numbers"
4. Edit > Paste Special, Multiply.

If you are dragging to select the "numbers" anyway, no need to select constants.

 

[kpetursson]

Try pressing "F9"
If that worked...
Did you check "tools" -> "options" -> "Calculation" -> "calculation" should be set to automatic?


Kevin Petursson
--
"Everyone says quotable things everyday, but their not famous... so nobody cares."... Some Person

 

[JBruyet]

Lilliabeth, Excel is definitely seeing the 1 as a 1 and not as text (I did a three cell sum and it worked), but I'm still getting the zero when I do the Paste operation.

kpertursson, at what point in this process am I supposed to press the F9 key? I tried it where my previous instructions said to press F5 but nothing happened. And fwiw Calculation is set to Automatic.

Out of curiousity I put the 1 next to the cells containing the data and included the 1 in the selection, but again I get the zero.

Thanks,

Joe Brouillette

 

[KenWright]

Do you know how to use code? I use the following routine any time I pull data from the web, as it cleans out all the garbage and converts it back to numeric:-

Sub a_TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim cell As Range
    'Also Treat CHR 0160, as a space (CHR 032)
    Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
                      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    'Trim in Excel removes extra internal spaces, VBA does not
    On Error Resume Next   'in case no text cells in selection
    For Each cell In Intersect(Selection, _
                               Selection.SpecialCells(xlConstants, xlTextValues))
        cell.Value = Application.Trim(cell.Value)
    Next cell
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

You should certainly not be getting zeroes

When you paste special, make sure you choose Multiply AND values

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JBruyet]

KenWright, I don't know how to do coding. I've had two quarters of C++ a few years ago and that's it.

xlbo, I'm making sure I do Paste Special, and I'm choosing Multiply and Values, but all I get is a zero in my target cell. I've even tried copying and pasting the 1 to a cell, then go back and copy the 1, select the imported data and Paste Special into the cell where the Pasted 1 is. I don't get a zero--the 1 is left intact.

Thanks for all the help you guys/gals!

Joe Brouillette

 

[AnotherHiggins]

Just to clarify the 'copy blank cell' vs. 'copy a cell containing 1' - you can copy a blank cell, then Paste Special > Add. Adding zero is essentially the same as multiplying by 1 - both force the destination cell to a number without changing the number in that destination cell.

JBruyet, have you tried the steps when selecting a single destination cell?

Here's the routine again:
[ul][li]In a cell to the right of all the imported data, type [blue]1[/blue][/li]
[ul][li](this should be right-aligned, indicating that the cell is formatted as a number)[/li][/ul]
[li]copy that cell[/li]
[li]select the cells that are giving you trouble[/li]
[li]Go to Edit > Paste Special > Multiply[/li]
[li]Now try applying a new format to those cells [/li][/ul]

Regarding Ken's suggestion - try this: enter [blue]=len(A1)[/blue] into an empty cell, replacing A1 with a cell that contains an imported number.

Please report back what number was in the imported cell and what number is returned by that formula.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[JBruyet]

Anotherhiggins, I followed your steps and after I Pasted Special I had a zero in my target cell. Would you (or anyone else) please double-check that the steps I have listed in one of my previous posts (the fifth post in this thread) are correct? I feel like I'm just missing something here but I can't figure out what it is.

As to the second part of your post, the imported number was 532 and =LEN(B8) returns a 4.

Thanks everyone!!!

Joe Brouillette

 

[byundt]

One of the things that Ken's code does is to replace ASCII 160 characters (which look like a space) with real spaces. I can replicate your problem if I precede each number with an ASCII 160 "space" (which are often encountered when pasting from web pages). If you don't want to bother with this step, then try the following:
1) Select some of your cells
2) Open the Edit...Replace menu item
3) In the Find field, hold your ALT key down, and enter 0160 using your numeric keypad. It must be with the numeric keypad rather than the numbers above QWERTY!
4) In the Replace field, don't put anything
5) Click the Replace All button
6) If arithmetic still won't work, repeat the suggestions made above for copying a blank cell, selecting your "numbers, then doing an Edit...Paste Special...Add

Brad

 

[AnotherHiggins]

LEN is short for LENGTH.

What that means is that there are four characters in the cell even though you only see 532 (three characters). The extra character is probably just a space before or after the number. That causes the cell to be considered TEXT, not a NUMBER (but you already figured that out).

The normal behavior for a text cell that is multiplied by 1 is to remain unchanged (DOG X 1 = DOG). The fact that your text cell is turning into zero is certainly strange, but we can ignore that problem and get you on the right track by converting the text "532 " to the number 532.

The easiest way to accomplish this is by using code. Don't worry, this will be relatively painless.

With the troubled workbook open:
[ul][li]Go to Tools > Macro > Record New Macro[/li]
[li]In the Store Macro In box, choose Personal Macro Workbook[/li]
[li]Press OK[/li]
[li]A new toolbar will have just appeared at the top of your screen[/li]
[li]Press the STOP button (it should be a blue square, like you would see on a CD player)[/li]
[li]press [Ctrl]+[F11][/li]
[ul][li]this will open the VB Editor, or VBE[/li][/ul]
[li]Press [Ctrl]+[R][/li]
[ul][li]this will ensure that the 'Project Explorer' is open[/li][/ul]
[li]In the window to the left, right click on 'VBA Project(PERSONAL.xls) > Insert > Module[/li]
[li]In the blank window on the right, copy 'n' paste Ken's code.[/li]
[li]In Excel, select the imported numbers that are giving you trouble[/li]
[li]Run the Code (Tools > Macro > Macros, select a_TrimALL, then press Run[/li][/ul]

Now follow the 'Times 1 Fix' instructions that have been posted a few times already. Everything should work fine!

FYI: I have the code Ken provided mapped to a button on a custom toolbar and I use it often. If this is a process that you will have to repeat, feel free to post in the VBA Visual Basic for Applications (Microsoft) Forum (forum707) for help with making a new button so you can easily access this code.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[bluedragon2]

Easy way is if you have <space>532 in cell A!, in cell B1 put VALUE(A1) and it should convert to the number 532.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[byundt]

I'm betting that either CODE(A1) or CODE(RIGHT(A1,1)) will return 160 (replace A1 with a reference to the cell containing 532).

 

[JBruyet]

OK!!! I just did byundt's suggestion and that worked. I see also that I now have three characters in the cell rather than four. Thanks byundt!

Anotherhiggins, I've done macros before in Office but it's been almost as long since I've done C++. I'll work on inserting the code and attaching it to a button. Thanks!

Bluedragon2, I'll keep your tip in case I just have one or two cells that I need to convert. Thanks 2 U 2!

Thanks everyone!!!

Joe Brouillette

 

[bluedragon2]

side note, you can do thousands of cells in less then a minute with copy and paste.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[JBruyet]

Bluedragon2, I don't think the people who need to do the conversions are capable of figuring out where to put the cells and how to arrange them to convert blocks of data. They CAN select a block of data and click a button without too much trouble so that's the way I think I should go.

Thanks again,

Joe Brouillette