In a series of short tips, we introduce the Paste Special feature.
A quick and easy way to multiply an array of numbers by a constant without using a formula…
In the example below, we wish to multiply B5:B20 by 10. Temporarily enter 10 into an empty cell. Select it and then Cut or Copy
Select the target range, then click on Edit – Paste Special . In the Paste Special dialog, unselect Paste All and Formats. Also select Multiply. Now click OK …
To create a series of dates in a range, with just weekdays (Monday through Fridays), you first enter your starting date in a cell (B2, in the example below), and then enter the following formula in the cell below that cell.
The WEEKDAY function returns an integer that represents the day of the week. The usefulness of this function is in identifying weekends in a series of dates.
It is straightforward to use the filldown functionality of Openoffice Calc to create a column of consecutive dates. We can use the WEEKDAY function to only include the days of the week we want – for example excluding weekends.
The arguments to the function are :
WEEKDAY(Number; Type) where
Number, as a date value, is a decimal for which the weekday is to be returned.
Type determines the type of calculation. For Type =1, the weekdays are counted starting from Sunday (this is the default even when the Type parameter is missing). For Type =2, the weekdays are counted starting from Monday =1. For Type = 3, the weekdays are counted starting from Monday = 0.
In the example below, we use the WEEKDAY function to identify weekend days.
We now use the WEEKDAY function to create a column of dates that exclude the weekends. An alternative (but similar) approach is shown here
Splitting an address (US) into separate cells
The FIND, LEFT, MID, RIGHT and LEN text functions are extremely useful and versatile. We have seen before how to split a person’s name into it’s component parts.
Here, we take a single cell containing a US address and split it into it’s City, State and Zip components. As long as the string we want to partition is well beahved – as is the case here, splitting should be relatively straightforward with the appropriate combination of text functions.
If, for example, we did not have a comma after the city, we could use the formulae below to extract the city and state components… =LEFT(B3;LEN(B3)-9) and =MID(B3;LEN(B3)-7;2)
Also, if you want to delete column B, you will first need to preserve the columns you have just created by doing a Paste Special (values only) as shown here
I think we need to explain the above formula a little bit. First off, when trying to develop formulas of this complexity – do not attempt to write it all at once.
This is what is known as a megaformula – a miniature program – squeezed into a single formula.
When extracting the last name like this – we use the RIGHT function – but it is not much use unless we know where the last name starts. It starts immediately after the last space character. We can find that with the FIND function. Howver FIND works from the left. If we could figure out how many space characters there are – we would be set. We do that by removing the space characters and seeing how much shorter the name is.
The whole procedure is illustrated below..
As an alternative to developing these megaformulas, you could also code this in Open Basic – but for that, we will wait another day.
The paste special feature is useful for preserving the values in a spreadsheet – but removing the formulae that generated those values. This may be required to preserve proprietary equations.
To remove the formulae for a range of cells, select and copy the desired range. Do not unselect the range. Now click on Edit – Paste Special – see below.
Make sure the Formulas box is unchecked. After you click OK, the formula is removed but the value is preserved.
