By combining the different text functions available in OOo Calc, some common name transformations can be performed.

Here are a few examples…

Using “Harry Potter in F1…

=LEFT(F1;FIND(” “;F1)-1) returns “Harry”

Note that without the -1 in the above formula, we would get a trailing space – making life difficult for us later on.

=RIGHT(F1;LEN(F1)-FIND(“*”;SUBSTITUTE(F1;” “;”*”; LEN(F1)-LEN(SUBSTITUTE(F1;” “;””))))) returns “Potter”

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.