Paste Special Multiply on blank cells

[EdStockton]

I am attempting to convert cells that are stored as text to numeric values. I have spreadsheet with cells showing numbers as text. I format a cell as numeric, copy that cell, select the cells to convert and go to Paste > Paste Special > Multiply.

The cells in columns with text in them are converted to numeric values. The cells in that column without text are not converted to numeric values.

Cells in columns with no values at all are converted to numeric values.

Why won't the blank cells in columns with text convert to numeric values.

The following illustrates my problem. Before I use the Copy > Paste > Paste Special > Multiply function. I used the formula =isnumber() in cells H3 through L8 referring to cells A3 through E8 respectively.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24,341
[/TD]
[TD][/TD]
[TD][/TD]
[TD]24,341
[/TD]
[TD]24,341
[/TD]
[TD][/TD]
[TD][/TD]
[TD]False
[/TD]
[TD]False
[/TD]
[TD]False
[/TD]
[TD]False
[/TD]
[TD]False
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[/TR]
[TR]
[TD]787
[/TD]
[TD][/TD]
[TD][/TD]
[TD]787
[/TD]
[TD]787
[/TD]
[TD][/TD]
[TD][/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[/TR]
[TR]
[TD]25,128
[/TD]
[TD][/TD]
[TD][/TD]
[TD]25,128
[/TD]
[TD]25,128
[/TD]
[TD][/TD]
[TD][/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[TD]False<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]

I then format a cell elsewhere on the spreadsheet as a number, copy that number, select cells A3 through E8, and then use the Paste > Paste Special > Multiply function with the following result.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24,341.00
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]24,341.00
[/TD]
[TD="align: right"]24,341.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]True
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00<strike></strike>
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]False<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]False<strike></strike>
[/TD]
[TD="align: right"]False<strike></strike>
[/TD]
[/TR]
[TR]
[TD="align: right"]787.00
[/TD]
[TD="align: right"]000
[/TD]
[TD="align: right"]0.00<strike></strike>
[/TD]
[TD="align: right"]787.00
[/TD]
[TD="align: right"]787.00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00<strike></strike>
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]False<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]False<strike></strike>
[/TD]
[TD="align: right"]False<strike></strike>
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00<strike></strike>
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]False<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]False<strike></strike>
[/TD]
[TD="align: right"]False<strike></strike>
[/TD]
[/TR]
[TR]
[TD="align: right"]25,128.00
[/TD]
[TD="align: right"]0.00
[/TD]
[TD="align: right"]0.00<strike></strike>
[/TD]
[TD="align: right"]25,128.00
[/TD]
[TD="align: right"]25,128.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[TD="align: right"]True<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]

Why don't the empty cells in columns with numbers convert to numbers when the empty cells in columns with no numbers do convert to numbers?

 

[pgc01]

Why don't the empty cells in columns with numbers convert to numbers when the empty cells in columns with no numbers do convert to numbers?

Hi

I'd say that the cells in the columns with numbers are not empty.

That is easy to confirm. In the first table instead of ISNUMBER() use ISEMPTY().

Please try.

 

[EdStockton]

pgc01,

You are right, they are not empty. However, when I go to those cells and do an F2, I can't find anything to delete. If I do an F2 and then hit enter, the cell becomes empty.

Is there a way I can find those cells, ones without numbers, and make them truly empty?

Thank you, Ed Stockton

 

[pgc01]

Hi Ed

I'm sorry about the ISEMPTY(), I meant ISBLANK() but I see you figured it out.

Those cells have probably an empty string, which you can confirm with ISTEXT() (=TRUE) and LEN() (=0).

The way you can find them:
- first you multiply all the cells by 1, like you did, getting the second table
- in the second table all the cells have numbers except those that seem empty but are not, as I said they probably have an empty string
- select the table and use GOTO (F5). Press Special and check Constants and uncheck Numbers, Logicals and Errors. You are left with Constants Text which is what we want.
Press OK and all the cells with a text constant (the ones we want) are selected.
Press delete. This clears all those cells.
Now you can repeat the multiply by 1 and you'll get 0 in those cells.

Seems a lot of operations but it's not. After you did it once you'll see it's quick and easy.

 

[EdStockton]

Thank you. That does work. I was hoping for an easier solution, but we don't usually get easy solutions.

Sincerely, Ed Stockton

 

[pgc01]

I'm glad it helped.

If you have to do this many times you could also write a vba snippet to do it.

 

[EdStockton]

I'm glad it helped.

If you have to do this many times you could also write a vba snippet to do it.

Thank you, but I don't have this come up too often. I am primarily trying to figure out Excel so I can work around it.