CODEWITHSUNDEEP
excel
jrara
jrara

Reputation: 16981

How to prevent numbers to get converted to dates in Excel?

I'm trying to copy paste numberic data from database to Excel. In the database the decimal separator is dot (.) and in my Excel the decimal separator is comma (,). Now when I copy paste this data from the database to excel, numberic data is converted to dates. How to prevent this and get data converted to numbers (with comma decimal separator)?

Data:

1.2
1.3
1.4

Converts to (in my locale)

1.helmi
1.maalis
1.huhti

Although is should be

1,2
1,3
1,4

Upvotes: 3

Views: 12495

Answers (5)

Alekzander
Alekzander

Reputation: 946

Microsoft advices to format cells as text and paste values after that:
https://support.office.com/en-us/article/stop-automatically-changing-numbers-to-dates-452bd2db-cc96-47d1-81e4-72cec11c4ed8

Upvotes: 0

Zen At Freemail Gr
Zen At Freemail Gr

Reputation: 1

  1. Open Microsoft Word
  2. Paste your data from web
  3. Open excel
  4. Format all as text
  5. copy all from word (Ctr+A)
  6. Paste special in Excel as text vuala!

Upvotes: 0

Hobs
Hobs

Reputation: 1

First mark the cells that you're going to paste to. Right click, "Format" and then select "Number".

Now right click to paste, and select "Match Destination Formatting"

Your text will now be pasted as numbers again :)

Upvotes: 0

Jook
Jook

Reputation: 4682

Yeah, this is a real pain sometimes - my quick solution is to use cells formatted as text.

Here is how it works:

  • copy your numbers with the .
  • select a column or some cells
  • change cell-format to text
  • now paste-special (or paste-contents) as unformatted/plain text
  • now replace . with ,
  • now change format back to number or default

You can put this in some macro of course.

However, I think there might - or at least should be - a better solution.

But as a quickie, this works too ;)

Upvotes: 0

Christian Sauer
Christian Sauer

Reputation: 10889

I use Notepad++ for this task: copy your data to notepad++ replace the "." with "," and copy it to excel.

Upvotes: 1

Related Questions