We will use this part of the course to introduce a bit of programming and to illustrate the use of the string functions introduced in the first week on the module. Feel free to skip this part if you have experience with programming and processing textual data.
Using strings and string functions in a spreadsheet
In a spreadsheet, strings are typed directly into cells. In fact, any information that cannot be interpreted as a number, a formula, a date or a logical values, is taken to be a string (text).
Any entry can be forced to be interpreted as text by writing a single quote as the first character or by entering it using a formula. In this case the text must be quoted with double quotes.
Figure 1. Main string functions in a spreadsheet.
Figure 1 shows how the different functions are used in a spreadsheet to manipulate textual data. On the left, the formulas are shown; on the right, we can see the results of each formula.
If you have never used these functions, please take some time to replicate Figure 1 and to make yourself familiar to these functions. More information on string functions in spreadsheets can be found at http://www.excelfunctions.net/Excel-Text-Functions.html and https://help.libreoffice.org/Calc/Text_Functions.
A minimal introduction to programming UDF (User Defined Function) in Microsoft Excel for Windows and LibreOffice/OpenOffice Calc
As you may know, spreadsheets can include programming code to extend its functionality. Although different types of programming can be achieved within a spreadsheet, the most common of them is coding user defined functions (UDF). This allows us to create new functions in spreadsheets.
For simple UDF, the flavors of Basic included in Microsoft Excel for Windows and in LibreOffice/OpenOffice are identical. In both cases, the first steps are to open the Basic IDE (Integrated Development Environment) and to add a New Module.
In Microsoft Excel, this is done pressing Alt+F11 and then inserting a module. In LibreOffice/OpenOffice, we will go to the Tools/Macros/Organize Macros/LibreOffice Basic dialog and there add a new module and press Edit.
Figure 2. A simple example UDF
Figure 2 shows a simple UDF. Copy it in your module and it will then be usable in formulas in your spreadsheet as the function nameAnalyzer (The name is given in the first line of the code).
More information about Excel VBA and LibreOffice/OpenOffice Basic can be found at https://msdn.microsoft.com/EN-US/library/ee861528.aspx and https://help.libreoffice.org/Basic/Basic_Help.
A minimal introduction to programming in JavaScript
To program a similar functionality in JavaScript (JS), you need to open a text editor (e.g. Notepad on Windows, Vim, LeafPad, Kate or gEdit on Linux, TextEdit on MacOS-X...) and copy the text shown on Figure 3.
Figure 3. A simple example script in JavaScript
As you can see, JavaScript is coded inside an HTML document. Although it can be used in other contexts, JavaScript is one of the key languages of the web with HTML and CSS.
Save your text file using “html” or “htm” for its extensions and open it with a browser. You have coded a HTML/JS page! (A very simple one though).
More information on Javascript is available at http://www.w3schools.com/js/, https://www.codecademy.com/learn/javascript and http://eloquentjavascript.net/.