Excel Worksheet Tips: PDF Table to Excel – UPDATE On occasion I need to pull in a table of data from a PDF file into an excel spreadsheet to manipulate the data or to add to it. However, highlighting the table in the PDF, copying and then trying to import into excel doesn’t work. It…
Read More
Excel VBA Tips: Stop Users from Adding Sheets Do you have a spreadsheet that you want to stop users from adding sheets to? Of course, you could lock the workbook using the “protect workbook” feature and ensuring that the protect structure is selected. However, say that you need to protect the workbook via vba. Place…
Read More
Excel Worksheet Tips: Remove First and Last Character From a String Perhaps you have a list of data that you wish to remove the first and last characters. For example, there is a list of values similar to these: “Data” “Trial” “Test” What you are after is the strings inside the inverted commas. You could…
Read More
Excel VBA Tips: Get Shape Properties Lately, I have been working with shapes in excel – moving, creating, making them invisible and resizing them. This seems easy, but because excel codes the location of an object from the top left of cell A1, it makes it very difficult to work out exactly where the object…
Read More
Excel Worksheet Tips: Reversing Rows of Data OR Columns of Data – Version 2.0 After creating a procedure to reverse rows or columns of data, I was informed of a much simpler way to do this. Reversing Rows of Data If you have a list of data in multiple rows that you wish to reverse,…
Read More
Excel Worksheet Tips: Reversing Rows OR Columns of Data There are times when you realise your list of data is in the wrong order; for example in rows 1 through 4 you have D then C then B then A. You realise you need the list to be reversed, with the order being A then…
Read More
Excel Worksheet Tips: Switch Between Formula and Results If you have created a worksheet in excel with formula all over the sheet and want to quickly see what the formulae are, use: Ctrl + ~ (Where ~ is tilde; which is located underneath the Esc key) You can even print in this mode and the…
Read More
Excel Worksheet Tips: Unlocking Specific Areas of a Protected Worksheet If you create a workbook that others are going to use, but you don’t want them to corrupt certain parts of the workbook, you will want to protect the workbook. However, if they are required to modify cells, having a locked worksheet will stop any…
Read More
Excel Worksheet Tips: Extract Date or Time from Date/Time Do you have a cell that contains both a date and time value but only need one or the other? To extract the date Assuming the date time value is in cell A1, you can use the following formula: =DATEVALUE(MONTH(A1) & “/” & DAY(A1) & “/”…
Read More
Excel VBA Tips: Convert Number to Text I have covered how to do this using worksheet formulae, but it can also be done using vba. If your variable (that is a number) is called Number, to convert this to text, use the following code: Number = CStr(Number) It will now be stored in text format.…
Read More
SUBMEG 