Number Data Does Not Copy Correctly

rebel123

Board Regular
Joined
Apr 18, 2017
Messages
88
I have my data on Google Sheets, and I need it on MS Excel.

Here is what I have tried.
Copy and paste (multiple formats like number, plain text, HTML).
I also copied and pasted it into an email and onto notepad.
I also directly downloaded the file from Google Docs as an XLSX
format, and that did not work.

On Google Docs it does not show dashes at all.
All of my data is zipcodes

My first sets of data are literally copied just like this.
Here is the HTML paste:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
72103
72218
72406
0*2048
02181*

<colgroup><col style="width: 100px"></colgroup><tbody>
</tbody>


Here is the PASTE VALUES ONLY PASTE
72103
72218
72406
0*2048
02181*

However, here are my actual results in Excel, which is a joke!

72103
72218
72406
0-2408
02181-

As you can see, the last 2 results listed below are completely wrong:
0-2408
02181-


Also, further down the list here is another zipcode
as it appears in Google Docs:
1*1250
1*2407

But instead read like this:
1-*1250
1*-2407



Please help!
Thanks!
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

rebel123

Board Regular
Joined
Apr 18, 2017
Messages
88
Whoops- even this copy and paste function does not work on here.
So, I am doing it manually.

It looks like this:
72103
72218
72406
02048
02181

And in the other part, where I say further down the list here is another zipcodeas it appears in Google Docs:

11259
12407

These are the way they literally look.

Also, I double-clicked on the cell in Google Docs
and there are no back spaces or spaces and there
are no dashes.
This is wierd!

Also there are no * signs only - signs.
 
Last edited:

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
1. If your numbers, either IN google docs, or in the copy across to excel, begin with a 0 - in the cell, not just the display, they are text not numbers, and no amount formatting will change that.
2. If your numbers, either IN google docs, or in the copy across to excel, contain * or any other non-number - in the cell, not just the display, they are also text not numbers, and no amount formatting will change that.
3. Test a few (that look off) with =isnumber(cell-ref) If you get FALSE, then you are dealing with text
 

rebel123

Board Regular
Joined
Apr 18, 2017
Messages
88
I am getting a mixture of Trues and false.

I think i figured out the best answer, but I am not 100% sure how to do it.

Wouldn't this make the most sense...


I should copy and paste the date into excel leaving the dashes.
Then, I should just do a formula to delete all of the dashes.

I tried find and replace on my MAC computer, and I have no
idea why it's not working. I did FIND AND REPLACE then
I entered - and then left the next line blank.

Usually that deletes it.
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723

ADVERTISEMENT

When you copy into excel, those cells that have - do they really have that -? Look up in the formula bar to check. If it is not there, then it is probably formatted as post-code
 

rebel123

Board Regular
Joined
Apr 18, 2017
Messages
88
Yes it really does have the - symbol (meaning the dash symbol).

I triple checked Google Docs and it does not have the dashes at all.

For example, when i copy it into notepad there is no dashes.
So that makes no sense at all.

And like I said, I am just going to find a way to delete all of the dashes.
Maybe my MAC computer is being dumb, but the FIND AND REPLACE
function literally does not work. And I have the newest paid MS Excel version.

Thanks!
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
If the F/R wont work, perhaps the - is something other than a regular -?
Try and isolate it with something like this?

A​
B​
1​
0-2408
45​
2​
02181-
45​
B1=CODE(MID(A1,2,1))
or
B2=CODE(MID(A2,6,1))

If you don't get 45, then you need to use that character in F/R

hmm of just copy that character from the cell, and then use that in the F/R
 

Watch MrExcel Video

Forum statistics

Threads
1,108,584
Messages
5,523,720
Members
409,532
Latest member
Lmfacc

This Week's Hot Topics

Top