I have a template that works from Excel, but is saved in a .csv by default because .csv is the only compatible file format with the database we use. I have one column that I need to be in a "text" format, and another column that must be in a number format with no decimal place approximations (whole numbers). I know how to go in and format these columns myself, but it is clear that .csv does not like it. Every time I open the file, it resets the formatting in my columns. So, I have to re-format these columns every time I open or re-open the file. If there's information in this file, I have to re-format my columns and re-enter my information so that it is formatted correctly. For example, my first column must be text because it will have things like 001,002,003 etc. I tried using a custom format ###, which did not work. If I save the file and leave it alone, it preserves the format when I import into the database. If I have to re-open it to add something or make changes, all of my first column info is now standard numbers like 1,2,3 etc. So, I reformat the columns, and I have to re-enter 001,002,003 etc. This gets annoying after awhile, especially if my sequences (numbers) go into the triple digits.
Obviously, there's no easy way around this due to the features of a .csv file. So, I would like to add code that executes on file start-up (or open). I want this code to format my columns as stated before, one column must contain text format, another has numerical format with whole number approximation.
I have several cells which I usually need to copy and paste. These cells contain single characters with a different format than the rest, as shown here:
inventar
not just with one word, but several words within a cell. The problem is that when I try to paste a part of a cell having multiple formats, it always pastes the text in uniform format, disregarding the format of those letters having a different format.
Does anyone know of a way to paste text having multiple formats when it consists of a part of a cell's content and not the full cell?
I have two cells. Cell A1 contains ‘The date is ’ cell B1 is formatted in the date format, 2007-01-24. I need to concatenate these two cells so that on cell C1 I have ‘The date is 2007-01-24’ but whatever I try excel keeps on changing the date to a text value and hence I am left with: ‘The date is ‘This is the date'39141 or this is the date 39141. how to get to the result that I am after?
I have a table with 15 columns and 10 rows (initially all cells are blank but with formulas and formatted) and I want a way to add automatically a new row in my table each time something is entered on first cell in each row. For example if I enter something in cell A1 (first cell in my table) then automatically add 11th row and if I delete the value in cell A1 it is ok to leave the 11th row there but if I I go on and enter a vlaue in A2 then add row 12th. Of course I want the new rows to have the same formats and formulas as the previous ones.
can anyone help me on how to create a formula or a macro that will automatically sort while preserving the relationships in rows without having to press any button? I have attached a sample workbook. Basically column ABCD under row 8 to 15 contains manual inputed values. While column ABCD beginning from row 24 to 26 should contains the automated sorted preserved relationship values.
I'm having a devil of a time trying to preserve the formatting on a pivot table that I made in Excel 2003. As near as I can tell, I've done the appropriate things --- check the preserve formatting box, uncheck autoformat --- but my formatting is not preserved when I pivot.
Adding to the mystery is the fact that some formatting is preserved but not all. I'm guessing either I'm missing something obvious or this is just an unruly, rogue pivot table that refuses to be formatted.
I have a very large table with 7000 rows and each time I open the file it takes excel about 1 minute to open it.Initially all cells in the rows are empty. Each row in the table has the same formats and formulas(eg. in J106 I have =IF(C106=1,1,"") and in J107 I have =IF(C107=1,1,"")).So the excel allocates all the memory and does the formatting and computations beforehand. How can I add new rows(preserving the formats and formulas of previous ones) dinammycally and automated? An example will be if I start enter data in a row add another row at the end of the table or a button that when I press it adds lets say 100 new rows in the table. Below is the table, the rows in the table to which I add data start form C15 to C7014.
I have an Excel 2010 spreadsheet where the default number format keeps switching to the date format. I keep switching it back and it'll stay as a number format for a time, but then it'll switch back. This is also the case for any new worksheets in that one spreadsheet.
This only happens with this one file - everything else behaves according to my regional settings or to how the file had been previously formatted.
Is there any macro that can be used to convert data which have been converted from PDF to Excel. Currently I'm using a traditional way by pressing F2 and enter to convert the data to excel format. It's tedious ( since the data range is quite big) and crammed my finger.
I have seen excel zipped but it is visible as excel only. when i double click it it open a folder where same excel document is placed i have to double click it again to open it. how can i save the excel as zip having its same format.
I have column A1 as 9/8/2012 11:00:00 AM. I would like to change it in the specific format as DD.MM.YYYY HH:MM, so I want it to read as exactly this way 08.09.2012 11:00.
I tried breaking the format as separate date and time n then joining back still no luck as when i delete columns A and B its a total disaster.
When I tried to import data in a CSV file, I ran into a couple of issues when trying to change the date format from MM/DD/YYY to other date format. I've already tried a couple things to no avail:
1. Use text to column functions and change the format from text to MMDDY 2. Use format cell function and change the date format 3. Use the text function (text(Cell,"MM-YY"))
Interestingly when I click on the cell and put my cursor and press backspace, the format will change. It seems like there's an extra space, but using the trim function does not work. I can't do this to thousands of data manually.
The starting sheet has lot of text cells with empty columns and rows between them, without pattern. I need to do the following:
-Copy the original sheet and work on the renamed copy -Remove all cells with bold font text (these are titles) -Create a new sheet -Transform the table to 1 column in the new sheet (no order required) -Remove empty rows -Save as CSV files with 2950 rows maximum each, with same name+number
I have attached a excel in this post. in that excel sheet, i need to take the input from the first sheet. The input contains special characters like : , ; , " .
I want to do the following operations.
1. replace all the special characters with line break
2. some email addresses prefix with To: . I need to remove "To:", "cc:", "1st support contact:"
3. after doing these operations , need to trim the column to remove unnecessary blank spaces.
4. remove the duplicates in the column
5. Sort them.
6. Need to remove some email addresses from the list. eg: "ocgsoy, Ggo", "IRFO"
I want to have the same format as the first one i.e., without the path. When the potx files are refreshed the link file will always be opened, so i do not need the path (this is part of a vba program).
The folder containing the files will be sent around and put in different drives.
'EHS Reports' is the main folder and that and everything else inside will remain the same. But the ppt links will not change so obviously will not update when the 'EHS Reports' folder is somewhere else.
I'm trying to export a couple of tabs in excel to PDF format. The code below works perfectly for a single tab, but I'd like to export two tabs into a single PDF document. How to tweak the code to allow a second tab to be exported at the same time? In both cases, I'd like to export the print area of the tab.
I have a excel workbook about 200kb in size. I have about 8 tables and a macro in it. Sometimes when i open it the default format changes to time format and i get values like this 00:00:00.
I have recorded a macro that will do the re-formatting for me.
Isn't there a checkbox somewhere where I can tell Excel 2007 to leave xls files as they are? My Excel 2010 doesn't urge me to save an old xls file format into xlsx but in Excel 2007 I can't find a switch (and I don't rememeber if I had to do anything in 2010). So ultimately I want to stay in compatibility mode when using old files.
I am trying to find a way in Excel 2007 to convert the date from US format to UK format, I tried to change it by using [right click=>Format cell=>Select Date=>Select the first option in Type:*14-03-01=>OK], but I doesn't work , the attached file are presenting the date in US format.
I want to take a copy of an excel document and send it to another user who want to see the values of the results and not have to have all the links and formulas. If there a way to save or publish a document so you change all the current values into values on the screen and remove all the formulas and links to other documents or workbooks that are based in my folders. I just want the values in another spreadsheet and it will show my tables and graphs in the same manner.
in the date fields excel only recognises some of them as date, so when I try to change the format of dates (mm/dd/yyyy to dd/mm/yyyy) it only changes some of them so I end up with some wrong dates.how I can make excel read them all as date?
Open a brand new spreadsheet whether the format is set to "General" or "Text" it will not let me enter numbers. For example, when I enter 2007 in the cell it displays 20 07 not really that big of space between the two zeros - but still a space. I can't format this no matter what I do.
i am trying to work on a database that has some errors that i want to correct the issue is that some of the cells have data that is listed last, first instead it needs to be listed into first last.
I can not seperate this into 2 fields as it would break the program this database belongs to.
this database is linked from excell to access i am using excell to update an access database
