How to prevent auto-conversion to date in clipboard paste special?

Hi,

First: Im in Finland, so the decimal separator here is comma -- not a dot!

So, I have an application that produces data containing product codes, names
and price information. The application uses decimal point, not comma.

In this application I'd like to paint, copy, and the in Excel 2000 "Paste
special" / "text" the data into Excel (data is conveniently tab separated so
it goes nicely into Excel cells), but:

One of the rows has price "12.5385" and if I "paste" or "paste special" /
"text" this to excel it converts into "Jou.85" (in english: "Dec.85")

Now then: Converting dot to comma has no effect after pasting the data to
Excel (other cells that Excel has not chosen to be dates convert nicely from
text to decimal values). Converting Format / cells / Number / Number
converts the original price of "12.5385" to "1272842". The magnitude of
error is drastic!

How can I guarantee that "Paste special" / "Text" does what it says:
"Inserts the contents of the Clipboard as text without any formatting" -- 
emphasis on word _text_ (i.e. not date!).

I know I can open the Notepad and paste the lot there, save as a text file
and open in Excel, and while importing a text file choose the related column
as "general" or "text". To me this is not a solution, nor chancing the
localisation parameters of Windows to accept dot as decimal separator for
all applications.

I use English Windows and Excel but the same happens with Finnish version of
Excel as well!

Pekka Siiskonen

[Mis-posted to "..worksheet.functions" first -- no intention to
cross-post -- sorry!]



0
psi (1)
12/22/2003 12:51:12 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
436 Views

Similar Articles

[PageSpeed] 25

"Pekka Siiskonen" <psi@mikrolog_dot_fi.no.spam> wrote in message
news:ukn5YpIyDHA.3224@tk2msftngp13.phx.gbl...
> Hi,
>
> First: Im in Finland, so the decimal separator here is comma -- not a dot!
>
> So, I have an application that produces data containing product codes,
names
> and price information. The application uses decimal point, not comma.
>
> In this application I'd like to paint, copy, and the in Excel 2000 "Paste
> special" / "text" the data into Excel (data is conveniently tab separated
so
> it goes nicely into Excel cells), but:
>
> One of the rows has price "12.5385" and if I "paste" or "paste special" /
> "text" this to excel it converts into "Jou.85" (in english: "Dec.85")
>
> Now then: Converting dot to comma has no effect after pasting the data to
> Excel (other cells that Excel has not chosen to be dates convert nicely
from
> text to decimal values). Converting Format / cells / Number / Number
> converts the original price of "12.5385" to "1272842". The magnitude of
> error is drastic!
>
> How can I guarantee that "Paste special" / "Text" does what it says:
> "Inserts the contents of the Clipboard as text without any formatting" --
> emphasis on word _text_ (i.e. not date!).
>
> I know I can open the Notepad and paste the lot there, save as a text file
> and open in Excel, and while importing a text file choose the related
column
> as "general" or "text". To me this is not a solution, nor chancing the
> localisation parameters of Windows to accept dot as decimal separator for
> all applications.
>
> I use English Windows and Excel but the same happens with Finnish version
of
> Excel as well!
>
> Pekka Siiskonen
>
> [Mis-posted to "..worksheet.functions" first -- no intention to
> cross-post -- sorry!]

Look at my reply there.


0
Paul
12/22/2003 12:53:47 PM
Reply:

Similar Artilces:

why do I get a different footer when copying and pasting text
I have a document that I want to copy chunks of into another document, but when I paste it into the new document it completely changes the footer and doesn't match the original or the new documents' footers - does anyone know why this happens and how I can stop this happening please? Hi Dawn, If the material that you are copying contains any section breaks, those section breaks will be copied into your document along with the configuration of the headers and footers that are embedded within them. A section break may look like a little thing, but a lot of information is...

split combined Time Date cells
from file dump have combined date time cells eg "14/04/03 14:20" (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) Hi Mark, With your date/time in A1, B1= Int(A1) Format as date C1=A1-Int(A1) Format as time Drag the formulae down as far as required. --- Regards, Norma...

XML to Excel XLS conversion
I have seen this question asked before on many newsgroups and websites but no one was able to answer this. The group I am working in is nearly finished a new reporting process that outputs all our groups reporting into excel files on a LAN drive. We are using SAS for report generation and would like to take advantage of the extra features in the ExcelXP tagset. For non SAS folks, it allows us to create spreadsheets in Excel XML format. The problem is the size of the output files compared to a binary xls file. They are just way too big. Some reach about 100 Mbytes in size. The only soluti...

Rank items, select one start date, have remaining dates follow based on rank
I have a list of projects. I have one start date. Each project has a varying amount of time to complete. I want the user to rank the projects based on importance (example when it will ship) and then, based on the individual times required to complete the rest of the projects, excel will automatically generate start and complete dates for the remaining projects. Need to know how to do this. -- rob normerica ------------------------------------------------------------------------ rob normerica's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26316 View this t...

Entourage Auto Spell Checker
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop When I type an email with &quot;HSA&quot; (this is what I want) in the text, the auto speller corrects it to HAS. How can I stop this from happening? <br><br>I turned off the &quot;check spelling as you type button--but it does this anyway. <br><br>How can I add this to the dictionary? Otherwise, sovle this problem. <br><br>Thanks. LAS@officeformac.com wrote: > When I type an email with "HSA" (this is what I want) in the text, ...

Auto formatting
I have to import/export files in and out of access and other programs using excel on a daily basis. All of these files are .CSV files. Some of the data in these files are the same as a date. For example; mar2 or feb-1. Both of these are product numbers and are not intended to be dates. When I open the file in excel it automatically changes them to dates. If I then highlight them and convert them to text I get a 5 digit number like "38413". So each time I open them I have to go through all the lines that I know were converted and change them back to the original data. If I forg...

Dates #2
When I enter "august25" Excel automatically changes the entry to "Aug-25". How do I change it so this will not happen? Hi precede your entry with an apostrophe -- Regards Frank Kabel Frankfurt, Germany Garrison wrote: > When I enter "august25" Excel automatically changes the entry to > "Aug-25". How do I change it so this will not happen? ...

Calculating age from date of birth and ignoring if blank
I've used the following formula to calculate a person's current age in years based on a previously entered date of birth: =ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0) However, if I haven't entered any data in the date of birth column, the result cell shows the age "110", which throws out other calculations. Is there any way to tell Excel not to enter any data if the date of birth is blank? Many thanks Hi Try this : =IF(P2="","",ROUNDDOWN(YEARFRAC(P2, TODAY(), 1), 0)) HTH John "news.eternal-september.org" <invalid@invalid.invalid> w...

Contact Auto Number
Hello, Does anyone have any clever ideas about how to configure a unique id field on the contact entity? What ive done so far is to add a new button via the isv.config which points to an asp page, which subsequently calls a stored procedure on a seperate database table(increments by 1 etc.,)and puts the value in the crm form. However, i am relying on the users to click on the button, ideally i would like it to populate the membership number field when the screen is displayed (and is blank). Anyone got any clever ideas? Jo H ...

Date Increment [If A Sunday]
Hi, Cell B3 is formatted as date [ddd dd mmm yy] and I select a date from a dropdown in B3 [set up thru Data Validation] I want cell B5 to equal B3 unless B3 is showing a Sunday, then I want B5 to increment to the Monday after the Sunday chosen [if B3 = Sunday then B3 +1 ,if you know what I mean] I've tried MATCH function [against a list of all the Sundays in the year] and IF statemnts but I'm going round in circles!! Any help greatly appreciated, Thanks, Bob. In Cell B5 (formatted as date) enter: =IF(WEEKDAY(B3)=1,B3+1,B3) HTH "Bob Beard" <bbib@talk21.com...

Visio Auto discovery
In Visio 2000 pro there was a network discovery tool that would interigate my network also an Active Directory tool. This has gone from 2003. I believe these functions are now supplied as add ons i saw an offer from Microsoft to get the add on for 2003 but can not find it again. No clear info on the Visio or Visiocafe sites that I can see. The offering was for a resource kit that included the low-end Fluke tool for LANs and the Altima Stencils. The kit does not support active directory (or the Novell one either). Al "Ian L" <anonymous@discussions.microsoft.com> wrote...

Disable Date/time field OnLoad
Hi Chaps Quick question, which I would be most grateful if anyone could help with. I am trying to disable a date/time field as part of a form OnLoad. However although the field is greyed out I am still able to populate data into the field and save the date on a save. The code I am using is crmForm.all.new_mydate.disabled=true; Is it just that this method does not work on a date/time field?.... or have I missed something. Thanks in advance John Hi, Could you try it with crmForm.all.new_mydate.Disabled=true; Could be you need a 'capital' D! Best regards, Merijn van Mourik...

the date format is so confusion....
Hi all, has anyone got a problem about the date format in excel document??? We are using business portal 4 and GP 10. Some queries (from BP4) generate report with date column on it. but the format of the date is so confusion. and some of the date is in US type, some of them in Aus type!!! It's soo hard to read... does anyone got any idea about how to solve the problem??? thanks!!! I'm not a "date expert" and I'm not a Business Portal expert J.Joe, but I have heard that date formats sometimes come from the workstation setup under control panel and regional settings...

Add future date with a button
Hi, I have a "last calibration" field on a form that is updated through the calendar control. Below that, I have a "Next Calibration Due" field that I would like to populate 1 year forward from the last calibration field. I used the expression builder "=DateSerial(Year ([StartDate]),Month([StartDate])+12,Day([StartDate]))" which works, however that field remains blank in the report. I thought perhaps just adding a button beside the "next calibration" field to calculate would be easiest. Thanks in advance, Chad Take a look at the Dat...

97 to 2003 conversion issue
I'm getting a lockup state when running a process in my database once updated. I believe it has to do with this code can someone give me some details. -------------------------------------------------------------------------------------- Sub GoToDesign() On Error Resume Next 'Ignore all errors, continue if possible... Call UserTrace([Screen].[ActiveForm].[Name], Me.[ActiveControl].[Name]) 'Fill tblAccessData with info DoCmd.Close acForm, "frmAbout" DoCmd.Close acForm, "frmSwitchboard" DoCmd.Close Call KillCommandBars Call SeeCommandBars Ch...

copy & paste using a formula ?
I am trying to do something that I can find no reference to! 1. I created a section on a sheet (reference area/list/table) with prefilled data. Say 10 c wide x 10 r high . 2. Each row is numbered ascending. 3. Each row is "named" as a range. 4. I have another section of the workbook where I wish to "copy a range" into. This section will be the same 10 x 10. I wish to input a number into a cell, (1-10) this # would reference a row of the data section. I need the program to "copy" the cell range to the new location. over simplified formula: =IF(...

Find/Replace Date Question
Using Excel 2003. Problem replacing a series of dates having a date format of dd/mm/yy. I want to replace all dates ending with /05 with /06. I enter in the Find box /05 and in the Replace box /06 Error is "Microsoft Office cannot find a match" I never had this problem with earlier versions of Excel. TIA Jim If those are real dates, try replacing 2005 with 2006. Jim wrote: > > Using Excel 2003. > > Problem replacing a series of dates having a date format of dd/mm/yy. I want > to replace all dates ending with /05 with /06. I enter in the Find box /05 > ...

how can you set up excel to auto-save a document?
Cherrice That would depend upon your version of Excel. There is no Autosave in XL2002 and 2003 as there was in earlier versions. Autorecovery from Tools>Options>Save is it. This is not the same as Autosave which made true incremental saves at intervals and alerted you before saving. Autorecovery just saves a temporary file which it deletes if Excel closes normally without incident. BTW.....Dave Peterson reports that he tried an earlier version of Autosave.xla in XL2002 and it seemed to work fine. I have also tried the Autosave.XLA from XL97 and does the job. To download the 97 ...

Auto name completion and .NK2 file
Hello all, I was helping a friend troubleshoot why her Auto name completion and name checking (in OL 2002) suddenly quit working. I verified all the appropriate settings. Then I checked into the .NK2 issue as suggested by the Microsoft website. She didn't have one but in my quest to discover her problem I discovered I don't have a .NK2 file either but by name completion and checking work just fine. I have "show hidden files" checked but an automatic and manual search come up empty. Any ideas?!?! Thanks. What OS are you using? Did you look for it in the right folder?...

Julian Date
Hi, How change a julian date to "normal" date ? Tks advance, Luis Felipe See http://www.cpearson.com/excel/jdates.htm for details. "Luis Felipe Alcantara" <lfvasconcelos@hotmail.com> wrote in message news:uYKyZmicGHA.3344@TK2MSFTNGP03.phx.gbl... > Hi, > > How change a julian date to "normal" date ? > > Tks advance, > Luis Felipe > Try this: For a Julian date in A1. Example: A1: 06127 B1: =("1/1/"&LEFT(A1,2))+RIGHT(A1,3)-1 In that example, B1 returns 38844 (May 7th, 2006) Note: Excel will engage it's def...

out of date items
I have a tools database with LastTestDate and NextTestDate fields, which is conditionally formatted for approaching NextTestDate. What I want to do now, is to run a query or report which tells me which items are out of date. I have a query which give me the NextTestDate: NextTestDate: DateAdd([PeriodTypeID],[Freq],[LastTestDate]) So if the NextTestDate is, say today, then I would like to be able to run a query which will sort out the items which are either out of date or will be very soon. Thanks Bob H wrote: >I have a tools database with LastTestDate and NextTestDat...

Numbers to dates
Hey all, I am trying to enter in #'s like 5-9, 10-14, 15- 19 in seperate cells. Now every time I do this it keeps changing the cells into date format. I've tried changing it to general and just number but it automaticly changes it to date. How can i stop/fix this? A. Precede the entry with an apostrophe ' or pre-format cells as text -- Regards, Peo Sjoblom "Andr´┐Ż" <anonymous@discussions.microsoft.com> wrote in message news:e25101c43c2c$47b728f0$a601280a@phx.gbl... > Hey all, I am trying to enter in #'s like 5-9, 10-14, 15- > 19 in seperate ce...

Auto Save Shared calendar daily
Hi all - I'm just trying to figure out if it's possible to save a shared calendar as a web page every day - automatically. I keep forgetting, and one of these days my computer's gonna spasm and our warehouse receiving calendar is going to be lost. Even if that doesnt happen, I'd feel so much better if I knew it was saving itself every day. Is there a way to automate that? Sherri ...

Date Range on report
I have a query that asks for a "begining date" and "end date" as parameters. Is there anyway to poulate these two dates on a report so that the user knows the time frame (they just keyed) of the report? I'm trying to summarize results for employees by week. Rey wrote: >I have a query that asks for a "begining date" and "end date" as >parameters. Is there anyway to poulate these two dates on a report >so >that the user knows the time frame (they just keyed) of the report? >I'm trying to summarize results for employees by week. ...

need an overdue date to highlight
trying to get conditional formatting to highlight a cell with an overdue date. Use formula is and =TODAY()>Date_Cell+30 -- Regards, Peo Sjoblom "Sarge" <Sarge@discussions.microsoft.com> wrote in message news:54017345-A39A-4115-B70A-C199265BE3F0@microsoft.com... > trying to get conditional formatting to highlight a cell with an overdue date. ...