Same data typing and copy-paste changes calculated value. Why?

Question

Hi,

Can anyone suggest, if I copy-paste a data then it shows a result but when I type manually same data then it shows a different result in Excel. Why the changes of data has been shown against same data with same format? Please suggest, it will help me more to work in Excel.

Answer 1

The reason is pretty simple: What you paste into excel is a text, not a value.

You can try to use TextToColumns to convert the text into values.
Convert numbers stored as text to numbers - Office Support

If that doesn't work means there are some invisible chars in the data, if you copy data from websites in many cases there is an ASCII 160.

In this case paste the data into an other sheet and call the UDF below to convert the text into numbers.

Andreas.

Function JustDecimalNumbers(ByVal What As String) As String
  Dim i As Long, j As Long, Digit As String
  Static Sep As String
  If Len(Sep) = 0 Then Sep = Application.International(xlDecimalSeparator)

  For i = 1 To Len(What)
    Digit = Mid$(What, i, 1)
    If Digit Like "#" Then
      j = j + 1
      Mid$(What, j, 1) = Digit
    ElseIf Digit = Sep Then
      j = j + 1
      Mid$(What, j, 1) = Digit
    End If
  Next
  JustDecimalNumbers = Left$(What, j)
End Function