# Stop From Converting Numbers To Dateformat

Oct 7, 2009I'm opening a file in Excel, and it automaticaally changes a lot of the numbers to dates. How can I turn this off in the settings panel?

View 10 RepliesI'm opening a file in Excel, and it automaticaally changes a lot of the numbers to dates. How can I turn this off in the settings panel?

View 10 RepliesADVERTISEMENT

I'm having a little problem working with some CSV files that are sent to us by an outside party. There is a field for an invoice number. This number should be a string since it sometimes has alpha characters and sometimes has dashes. But the field is not delimited to indicate this, so when opening these files in Excel, Excel will sometimes convert the numbers to dates. Once they are converted to dates, there is no going back.

The way we have gotten around this is to change the csv extension to txt, then open as a txt file and convert that particular column to a text column. This causes a lot of additional work as the file ultimately needs to be converted back to a CSV.

Ideally, the people creating the CSV files should create them so the invoices column is properly delimited. But that doesn't seem to be possible right now.

Is there a way to create a macro that would make the modification while opening the CSV file, before Excel has a chance to modify those fields?

I have looked at some threads related to this issue but don't quite fit what I am on about. I access some data online that appears in a grid format which I copy and paste to Excel. I don't have access to the source data/database. I select and set some query variables and run the query and the result appears in the grid. I copy and paste to Excel that is it. When paste to Excel 2010 I have the choice between "Keep source formatting" and "Match destination formatting". OK, I always choose the 2nd option i.e. "Match destination formatting". That is it. It appears to be a simpler format. Where does the problem come in? Can you help me stop Excel from involuntarily converting numerics to dates when not asked?

The data itself can (at least the part that I'm interested in can) come in 2 different ways. 1) WPS or 2) W%P%S. In case you're interested these represent racing statistics where 1) WPS = Number of Wins, Places, Starts given as whole integer format and 2) W%P%S = Percentage of Wins, Percentage of Places and S is still the integer number of starts. Now, when I run the query and copy these statistics over to Excel, particularly in the form of the 1st type of data i.e. WPS they are in the form e.g. "2-6-12" which means 2 wins, 6 places from 12 starts. The stats literally come across in the form "W-P-S" where I fear the delimiter "-" is causing the date conversion consequences that I have outlined at the start. ALSO, as you could have imagined, I have tried importing the data in terms of the 2nd type of data namely "W%-P%-S", which, on the surface of things begins to solve the problem in terms of the initial paste of the data into Excel. That is true, and yes I have found that this has delimited the data correctly mainly. Although there are still occasional problems. When I import the data this way, I still want the data in the % form as well as the integer form. So I do a great deal of data massaging with what I have and yet in re-converting some of the data back to integer form I can still encounter the "convert to date" problem.

http://www.excelforum.com/excel-gene...cognition.html

and im copying and pasting data from a website ( football scores )

and when i get what should be 1-1 it returns 01-jan and this i dont want

i have tried formatting all cells to text beforehand but that makes no difference and i cant put an apostrophe before each one as that would take ages

wondered if anyone could work out some syntax to use as a macro button?

claymation had a go but it doesnt work.

I'm looking for the VBA command for this function. I tried just recording a macro in where I perform the task but it didn't record anything. Tried it several times even.

View 7 Replies View Relatedwe work with both Lotus 123 and Excel 2003. Lotus will be gone next year, but for now, the official mean to publish our reports is Lotus. With my work, I copy/paste a Lotus page to Excel. I use the following macro to convert Lotus format numbers (which Excel considers as text) to real numbers:

Sub ForceToNumber()

Dim wSheet As Worksheet

For Each wSheet In Worksheets

With wSheet

. Range("IV65536") = vbNullString

.Range("IV65536").Copy

.UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd

End With

Next wSheet

End Sub

Source : http://www.ozgrid.com/forum/showthre...087#post184087. The problem is that I need to send back this data in Lotus. Excel considers decimal numbers with a coma as real numbers and numbers with a dot as a text. This previous macro fixes that. However, Lotus works the other way. Only numbers with a dot are considered real numbers. So I would need to find a way to code a macro that converts any numbers in the Excel sheet to a number with a dot. It's a bit like doing the opposite operation.

im working on making a price list from a pdf file. however, the pdf file has the prices in euros listed as 1.300,00 and 765,00 and 45,00 and 1.565,00 . i was wondering if there was a tool or so in excel that can change the comma and periods in the number so they read as american format like 1,300.00 and 765.00 and 45.00 .and also if there is a formula or so to convert these numbers to Dollars in another column.

View 6 Replies View RelatedI have a HTML table with a column with numbers like

3.6

2.5

1.8

....

and so on...

When I'm trying to open this table in Excel (.csv or if I cut n paste from HTML) Excel keeps converting it to date format.

03.jun

02.may

01.aug

This wouldn't be a problem if I could only go to Format -> Cell and choose Standard or something.

The problem is it ends up like this then:

39236

39204

39295

WTF? :D

I want this!

3.6

2.5

1.8

....

I'm having issues with my code for converting numbers to words I've tried a couple and I keep getting a Syntax error on

Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _

"00", 2))

I've tried a couple of different codes and I keep getting errors

yes i have enabled macros

I am receiving a monthly breakdown of postage costs by department but the values are received in a text format of 1 234,56.

I need to convert these to numbers.

I have replaced the comma with a full and tried to use =substitute(cell ref," ","") to remove the spaces in those numbers exceeding Â£1000 but this does not seem to work.

Postage.xlsxâ€Ž

How can i convert text columns to numbers in VBA.

View 8 Replies View RelatedI have 0,1,2,3,4,5...etc in Column A. How I could convert this into time format? Preferably with vba code?

Sheet1 Â AB

1001/01/2012 00:00:002101/01/2012 00:00:013201/01/2012 00:00:02

4301/01/2012 00:00:035401/01/2012 00:00:046501/01/2012 00:00:05

I have this value in a cell 38761.8296804781 which is displaying as 38,762. I want to display it as $38,761,829. Is this possible without going through each cell and moving the decimal point?

View 3 Replies View RelatedWe are doing a database conversion and I have been tasked with converting all our old promotion codes to a new system. The long and the short of it is this:

I am trying to write an equation that will look at two cells (say A1 & B1) which when put together could be one of the following numbers (01,02,03,04,05,06,07,08,09,10,11,12) and translate them to a letter (A,B,C,D,E,F,G,H,J,K,M,N)

So 01=A, 02=B and so on...

I was trying to use if statements, but there are too many.

Do you know if you can use the cell formatting to change the sign of a number? For example, the cell contains 123.45, but I want it to show as -123.45. I don’t want to use a formula to multiply by -1, because the data is being extracted from a database and I have to do a fair amount of work on the report with the formulas to do that.

I can format the number and it shows a minus but will not use the minus if I then use it in a calcualtion. I could use a calcualtion with a *-1 in it but for ease of use by other users who may take part of the Excel report it would be better if I could format the number to negative.

I am using decimal and hexadecimal numbers. On a worksheet I can use all of the normal functions for this problem (Hex2Dec etc). I have loaded the Analysis toolpack, and also have an add-in loaded (but do not know how or when) that is called Analysis ToolPack VBA. In VBA I can use the fnction Hex to convert from decimal to hex, but there does not seem to be a reverseof this function. Am I missing something obvious or is this a 'hole' in the available functions? Is there a reference I ned to add in the VBE? if I try to use the worksheet function in VBA then I get a Value# error.

View 7 Replies View RelatedI have five columns on an Excel Spreadsheet with questions in each of the columns.

Below each question is a number value. In the rows below these number values the users will be answering the questions with Y or N. When the spreadsheet is complete I would like to replace the Y's with the number value shown under the question and replace the N's with a number value of zero (0).

Is there any easy way to convert these?

I received a complaint from one of my colleague that he is facing sudden problem with Ms-Excel(2002)

The problem is When he feeds number 2 in to a cell it automatically converts in to 0.02. I tried using "Decrease decimal" and "format cells" option and even through uninstalling office and reinstalling a different version but the problem still continues.

I generate a range of random numbers in column C. Then I perform another task and then copy the randoms numbers in the range c4:c(some row) into another sheet.

But the two columns are not the same? I want to copy the same random numbers into the other sheet.

Code:

'Generate random numbers

Lastrow1 = Range("c65536").End(xlUp).Row

ActiveCell.FormulaR1C1 = "=RAND()"

Range("c5:c" & Lastrow1).FormulaR1C1 = "=RAND()"

'Finding a specific row number and copying random numbers

[Code] ........

I have exported a dataset with references numbers and I want to convert those reference numbers into the titles they refer to. I have a separate sheet that matches the reference number to the title and would normally use a LOOKUP function [=LOOKUP(A2,Key::Table 1::A,Title)] to do this but, in this instance, I have multiple reference numbers in a cell separated by commas. How can I get the function to lookup multiple numbers from the same cell?

View 1 Replies View RelatedI have numbers from -50 to 50 going down column C. In column D, I want to enter a formula which will convert each number to a TRUE or FALSE. How do I do that?

I believe 1 = TRUE and everything else = FALSE, but I would like to know how to get that with a formula.

How do I convert hours into numbers and/or decimals?

Example:

Column A Column B

---------- -----------

30:05:00 to 30.05

26:10:00 to 26.10

262:47:00 to 262.47

Figures under Column B refer to info that I would like to get.

how we could convert numbers to 24 hour time format as we key in the numbers?

Example:

As we key in 0815, it would automatically convert to 08:00.

As we key in 1345, it would automatically convert to 13:45.

What is the code we could use to put in the code of the sheet?

Has anyone created a marcro that will convert all the worksheets to Hard number, changing the formula results, (by Copying and paste, special, value). all at once?

I have a spreadsheet which has the date set out like this: 20070101

If there any way i can change this to either 2007/01/01 or 01/01/2007?

I have tried using the date format (in the cells) but it just comes up with ######## i tried expanding the cell but no luck, this is very importand that i do this as its for a customer.

Any easy function to convert numbers into words?

I a looking for an easy function to transform numbers into words as shown :

Value------Output :

12624-----Rs Twelve thousand six hundred and twenty four only

12624.24--Rs Twelve thousand six hundred and twenty four and twenty four paise.

Hopefully the currency should be changeable ( dynamic) i.e Rupees to Dollars..

In India , we also use lacs and crores , so something in those lines if possible...

I am having trouble converting time cells into numbers.

The cell I'm looking at visually shows "135:42:00". If you look at the formula bar, it reads "1/5/1900 3:42:00PM"

How do I start a series of conversions to get an adjacent new cell that would read 135.7?

I need a way to randomly generate a number( up to hundred trillion) in cell A1 and in cell B1 have the number appear in word form. Example: 129,114,023,131,453 will appear in A1 and in B1: one hundred twenty-nine trillion one hundred four-teen billion twenty-three million one hundred thirty-one thousands four hundred fifty-three The hyphens are important, commas between the numbers are not necessary. I know that for A1 I can use Randbetween() function to generate the numbers. The problem is generating the word form with the hyphens....

View 7 Replies View Relatedhow to create some code that can convert any column number to it's respective letter, from 1 = "A" to 16384 = "XFD". The code is posted below. Enjoy, or let me know how you would improve it.

Code:

Function colNumToChar(colNum) As String

Dim Num1, Num2, Num3 As Integer

Dim Let1, Let2, Let3 As String

[Code]....

I want to convert numbers that were entered into a spreadsheet with commas in them. Since they were entered with commas of course excel reads them as text.

How can I convert them to numbers so I can use formulas.

Copyrights 2005-15 www.BigResource.com, All rights reserved