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?
When opening a .CSV created from a third party software, one of the columns is converted to date format. The numbers are imported in #/# format. and Excel changes most of the numbers to 2-Jan, 3-Jan etc... This would not be that big of an issue if I could simply set it back. Clearing format or changing to text converts it to a 5 digit number.
The only way I have found to get it to work properly thus far is to open a blank sheet and import data and setting the column to text prior to import.
I would like to find a way of opening the CSV file without all the extra steps. Is there a setting that can be turned off?
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.
Today() provides a dynamic date which always equals the current date - not very useful when dating accounting documents. Is there a function that will allow the use of Today(), at the time of the document creation, but will then freeze the date to the date of creation?
I need to convert the following string into Excel dates:
Jan 1, 2009 Jan 12, 2009 Jan 21, 2009
Right clicking it and changing it to date format will not work.
I have tried extracting individual days/months/years out by using Left/MID and Right functions but as you can see, problem lies when there days with single digit or double digits.
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.
we 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.
Backstory: I am converting PDF documents to Excel files and some of the numbers are being transformed into a wonky date format which to the ***** eye looks semi correct but the underlying value in the formula bar is incorrect. Basically I need to copy and paste the value but when I paste special values it doesnt work. Below is the example
The cell value should be: 9856 When it is converted to excel the value shows 98 5 6 but in the formula bar it shows: 5/6/2098 The cell is formated as: Custom yy m d;@
If I get the value in the cell to read 98 5 6 I can use trim or something to get it down to 9856 but the issue is when I use those functions they return 72446. I guessing this is because they read the formula value not the cell value.
I need to somehow get the value in the formula bar to read 9856.
I have dates being imported (AS TEXT) to excel in various formats and want them all to appear in their cells in the same 8 digit format (MMDDYYYY) so it can be exported to another program
for example:
Single Digit Month and Day cell A1: 3179 (convert to 03011979) cell A2: 30179 (convert to 03011979) cell A3: 030179 (convert to 03011979) cell A4: 311979 (convert to 03011979) cell A5: 3011979 (convert to 03011979) cell A6: 03011979 (already correct)
Single Digit Month and Double Digit Day cell A7: 52165 (convert to 05211965) cell A8: 052165 (convert to 05211965) cell A9: 5211965 (convert to 05211965) cell A10: 05211965 (already correct)
Double Digit Month and Single Digit Day cell A11: 12523 (convert to 12051923) cell A12: 120523 (convert to 12051923) cell A13: 1251923 (convert to 12051923) cell A14: 12051923 (already correct)
Double Digit Month and Double Digit Day cell A15: 112180 (convert to 11211980) cell A16: 11211980 (already correct)
I'm a moderate user of excel with some experience but need help creating a specific if statment.
I have a column "A" that shows dates formated like this: 060115 (January 15, 2006). Because excel does not recognize this as a valid numerical date I need column B to show what year the date falls under. To complicate it I need it to reflect a seasonal year from April 1 YYYY, to March 31, YYYY.
So here's an example. Say I have a date in column "A" of 050612 (June 12 2005). I need column "B" to look at that date and determine if it's >040105 and <033106 and return a result of "2005". I then need the formula to calculate an answer for up to 5 years worth of returns.
I am working on combining several Excel spreadsheets and a report from a different program into one master Excel report. I have a macro built so the report can be compiled efficiently every quarter. However, I have one obstacle to overcome before I reach report programming bliss: The other program's report does not use a date format that Excel can interpret as a date. Excel interprets it as a value.
Specifically, the other program outputs September 3, 2009 as 9032009. It is crucial that the report be arranged in chronological order (time is a separate field). Does anybody know if there is a way to mass convert a bunch of values that look like 9032009 into 9-03-2009?
I have hit an issue when tranfering a string date from VBA to excel, excel thinks the string is US format and swaps around the days and months even though the system and excel are setup for UK date format:
Simple example of the problem:
Sub test() Dim testy As String testy = Now 'testy will equal 04/10/2007 17:20 Range("A1").Value = testy 'A1 will equal 10/04/2007 17:20 End Sub
Now I know in this example that if I used DateValue(testy) it would work, but our situation is a bit more complex as we are running this is a loop testy is not always a date.
The company I work for does not use the usual calendar dates and uses a modified calendar. As an example, the month of January is Dec 31 thru Jan 27, February is Jan 28 thru Feb 24 and so on. I need to group data using a pivot table and summarize data by month, but as I just described above, calendar months will not work. Is there a way to modify what Excel sees as monthly dates?
I need to convert numeric data to proper dates. Example: a cell currently reads 100875 but I need it to display 10/08/1975.
I've already found a VBA script that properly formats new data as you enter it (keying 1298 results in 1/2/1998), and I'm familiar with using =DATE(left,mid,right) to coerce Excel into spitting out a date in a certain format.
The difficulty I'm having is that I need to make existing data display correctly, without adding another column to accommodate reinterpretation of said existing data through a formula. Essentially I'm looking to avoid having to re-key several thousand date entries.
I get sent a spreadsheet by one of our partners where the dates look like they are in European date format except thay are actually in American format.
example
01/09/2007 - they mean 9th January 2007 but Excel holds 39326 which is 1st Sept 2007.
I can obviously format them but when I sort the list Excel still reads 1st Sept instead of 9th Jan
There are over a thousand entries so re-typing isn't an option.
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.
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.
