Thanks. It isn't a formula, just straight text or some weird formatting. Actually, if you copy the numbers below by double clicking on the number and pasting into excel it does the exact same thing (don't match destination formatting). If you highlight manually it works. Any clue why?
| *1096045508 |
| *1559772594 |
| *1580657761 |
| *1823876416 |
<tbody>
</tbody>
Ah!
Following your instructions... clicking on a number and then pasting into Excel does something interesting!
I think it's because Excel doesn't know what it is (because it contains some kind of additional formatting)...
You're not actually pasting a number... you're pasting some kind of HTML/Unicode information.
So.. as Excel doesn't know what to do with it.. it adds a little extra info.
What you see displayed in the cell is NOT what's actually IN the cell.
I used the following code
Sub test()
Dim x As Range
Dim y As Range
Set x = Sheets("Sheet12").Range("A1")
Set y = Sheets("Sheet12").Range("B1")
Debug.Print "Pasted: " & x.Value2
Debug.Print "Manual: " & y.Value2
End Sub
When you run it, it shows the following output:
Pasted: 1096045508
?
Manual: 1096045508
NOTICE THE ADDITIONAL QUESTION MARK
It's easy to see, now, that what's IN the cell where you pasted the data is not actually a number.. but some kind of "variant" data type that isn't really text as such. It defies functions that work on TEXT (even though ISTEXT shows TRUE!)
Another way you can tell this has happened is if you:
Click on the PASTED DATA cell and press F2 to edit.
Press the backspace key ONCE
Notice NONE of the number has been deleted... the "invisible" question mark has just been deleted!
Here's another way of looking at it:
Double click on the number.
Go to Excel to paste.
Select a cell
Use PASTE SPECIAL.
You get one of three options
HTML (Default, I think)
Unicode
Text (without formatting)
If you use Text - it appears WITH the question mark.
If you use HTML.. you don' t see the question mark because (I assume) this is Excel's way of saying "This is HTML and you don't want that extra bit seen" (i.e. it's about presentation, not numerical data)
Simple to fix, now we know it's got an extra bit of info.
Assuming your data is in column A, starting at row 2
in B2 enter
=LEFT(A2,LEN(A2)-1)
This will still be a text value.
From here, because it no longer has that question mark, you can perform the normal calculations on it.. like copy/paste special - multiply, or the =VALUE() function, and so on....
Hope that helps!
