The new version of Pandas uses the following interface to load Excel files: read_excel(‘path_to_file.xls’, ‘Sheet1’, index_col=None, na_values=[‘NA’]) but what if I don’t know the sheets that are available? For example, I am working with excel files that the following sheets Data 1, Data 2 …, Data N, foo, bar but I don’t know N a … Read more
I need to return an empty cell from an Excel formula, but it appears that Excel treats an empty string or a reference to an empty cell differently than a true empty cell. So essentially I need something like =IF(some_condition,EMPTY(),some_value) I tried to do things such as =IF(some_condition,””,some_value) and =IF(some_condition,,some_value) and assuming B1 is an … Read more
Is there an in-built function to check if a cell contains a given character/substring? It would mean you can apply textual functions like Left/Right/Mid on a conditional basis without throwing errors when delimiting characters are absent. 11 Answers 11
I would like to loop through the files of a directory using vba in Excel 2010. In the loop, I will need: the filename, and the date at which the file was formatted. I have coded the following which works fine if the folder has no more then 50 files, otherwise it is ridiculously slow … Read more
I have a large spreadsheet file (.xlsx) that I’m processing using python pandas. It happens that I need data from two tabs (sheets) in that large file. One of the tabs has a ton of data and the other is just a few square cells. When I use pd.read_excel() on any worksheet, it looks to … Read more
How do I return a result from a function? For example: Public Function test() As Integer return 1 End Function This gives a compile error. How do I make this function return an integer? 4 Answers 4
There are many, many questions and quality answers on SO regarding how to prevent leading zeroes from getting stripped when importing to or exporting from Excel. However, I already have a spreadsheet that has values in it that were truncated as numbers when, in fact, they should have been handled as strings. I need to … Read more
Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers. Want to improve this question? Update the question so it’s on-topic for Stack Overflow. Closed 7 years ago. Locked. This question and its answers are locked because the question is off-topic but has historical significance. It is not currently accepting … Read more
How can I construct the following string in an Excel formula: Maurice “The Rocket” Richard If I’m using single quotes, it’s trivial = “Maurice ‘The Rocket’ Richard” but what about double quotes? 13 Answers 13
I’m looking for a way to convert xlsx files to csv files on Linux. I do not want to use PHP/Perl or anything like that since I’m looking at processing several millions of lines, so I need something quick. I found a program on the Ubuntu repos called xls2csv but it will only convert xls … Read more
