I don't even know how to ask the question....

[robertdseals]

OK, I'll try this without screenshots.
I copy data from sheet1 and paste it into sheet2 as "values only". However, for some reason, the number isn't "formatted" correctly and I have to put my cursor at the end of the number and hit enter, then it is. I've tried pasting as "values and number format", "values only", using the "format painter" from other column and painting it into the new column. Nothing works except putting my cursor at the end of the number and clicking enter.
Thoughts?

 

[Jonmo1]

Sounds like numbers stored as text.
What 'exactly' was copied? Are they formulas?

After pasting, try
Copy a blank/empty cell
Highlight all the 'numbers' that you pasted
Right click - Paste Special - Values - Add - OK.

 

[joeu2004]

I copy data from sheet1 and paste it into sheet2 as "values only". However, for some reason, the number isn't "formatted" correctly and I have to put my cursor at the end of the number and hit enter, then it is.

My guess: the original data are text.

Verify by entering =ISTEXT(A1) somewhere, where A1 is one of the orginal data.

Every cell with a numeric format can have plain text. And the text can look like numbers, but it is still text.

By putting the cursor in the copied cell and pressing Enter, you "re-enter" the value as if you had typed it manually; and Excel re-interprets what you "entered" as a number.

The remedy might be to ensure that the original data are not text. How to do that depends on how you entered the original data in the first place.

At a minimum, if the original data are in a column, select the original data and use the Text To Column feature. Just press Next repeatedly, then Finish. TTC will convert numeric text to actual numbers, it possible.

Alternativey, enter the following formula into a parallel column:

=TRIM(SUBSTITUTE(A1,CHAR(160),""))

Then you can copy the results in the parallel column back and paste-value back into the column of original data.

The formula might do more than necessary. But it anticipates other possible problems that might prevent TTC from doing the job.

If this does not resolve all of your problems, upload an example Excel file to a file-sharing website, then post the public/share URL in a response here.

FYI, screenshots are often useless, especially with problems like this.

 

[robertdseals]

Yes, it does seem to be pasting a number as text (even though I'm telling to to paste the number format). Thanks, that will give me a starting point!

 

[joeu2004]

I copy data from sheet1 and paste it into sheet2 as "values only". However, for some reason, the number isn't "formatted" correctly and I have to put my cursor at the end of the number and hit enter, then it is.

Yes, it does seem to be pasting a number as text (even though I'm telling to to paste the number format).

To be clear, we're saying that the sheet1 data is text.

So when you paste-value into sheet2, it is stored as text because that is the type of the copied data.

As I said before, it does not matter what the format of the source or target cell is. We can enter text into any cell with a numeric format. For example, type "hi world" without quotes into a cell formatted as Accounting, then look at the cell format. It remains formatted as Accounting, but obviously "hi world" is not numeric.

The question is: what was the origin of the data in sheet1?

I suspect it was copy-and-pasted from another application (web browser, PDF, etc). Often, numbers are pasted as text in that situation, unless the numeric data appears in a structured table that Excel understands.

I try to avoid the problem by first pasting into Notepad, then copying the Notepad text and pasting into Excel.

There are many circumstances where that does not work or it is not feasible to do. But often, it is a quick-and-dirty solution.

Otherwise, it is just one of those things we must always be aware of when we copy-and-paste across applications.