In this month’s newsletter we cover the use of Text functions which are often used to analyse and tidy spreadsheet data. However, their most powerful application is in automating data entry, saving you hours if not days of time. If you have any requests or suggestions for future areas to cover, please just get in touch.
- The Concatenate Function
- The Find Function
- The Length Function (Len)
- The Mid Functions
- Combining these functions to save a lot of time, with our example of automating finding website addresses from email addresses.
1. The Concatenate Function
The Concatenate function will combine the values of cells as though they are text, not like adding or subtracting.
For example, if A1 were “Mr”, B1 were “Franc” and C1 were “Sutherland”. You could write the following equation into D1,
and upon hitting Return, the field D1 would show
Notice that there are no gaps. You must place the empty spaces (double quotes with a space in between) into the concatenate function, like this:
=concatenate(A1,” “,B1,” “,C1)
This time, upon hitting return, D1 will show
Mr Franc Sutherland
This formula can be copied down a column so that all the cells in this column will concatenate the values of the three cells to the left, e.g. D4 will show the values of A4, B4 and C4.
2. The Find Function
The Find function looks within a cell for a certain value or term, and tells you where within the cell it is.
For example, if there was an email address “firstname.lastname@example.org” in cell A1, and you wanted to find the position of ‘@’ within the cell, you would write in B1,
The first term within the brackets is the value, letter or symbol you are looking for, the second term is the cell you want to look in, and the third letter is the starting position within the cell for the find, in this case, 1, which means start at the far left. This is the normal starting position. As you can see from the image above, the value in cell B1 is ‘5’. This means that ‘@’ is the fifth character within the cell. You may ask “what use is that?”, but that will become obvious at the end of this Tip when we combine these four text functions.
3. The Length Function (Len)
This is the most straightforward of the text functions. It tells you the number of characters in a cell. For example, if in cell A1 you had “Working Data”. In cell B1 you could write
=len(A1), as below.
As you can see, the value in B1 is 12. That means that there are twelve characters within the cell A1. Seven characters in ‘Working’, four character in ‘Data’ and one character for the space in between.
4. The Mid Function
The Mid Function is a very powerful and versatile function which has many applications and becomes more and more useful with time and practice. It will give you a part of a cell’s value. For example, if we have ‘Working Data’ in cell A1 again. If you type
=mid(A1,9,4) into cell B1 and hit return, you will get the value ‘Data’, as below.
Let’s look more closely at the equation in cell B1,
=mid(A1,9,4). If we break it down, A1 is the cell we wish to investigate, 9 is the starting position and 4 is the number of characters to display. So, if we count the letters in cell B1, ‘Working’ is the first seven characters, the space is character number eight, and ‘D’ is character number nine. If you then count four characters from there, including the starting ‘D’, you get ‘Data’.
5. Combining these Text Functions to save a LOT of time!
To show how combining these four functions can save you a great load of time, we are going to look at the following scenario. Imagine you have been given a spreadsheet of around 4000 names, with email addresses, and someone says to you, “get me the website addresses for each of these four thousand people.” A business’s website address is usually the same as the second part, or “domain name”, of an email address. For instance, my email address is email@example.com and the company website is www.workingdata.co.uk.
So you have 4000 email addresses to read and then type the website address into the next column. How many days would that take you? At least one, maybe two? By combining these text functions that we have explored today, you would get the same result but within minutes. Let me show you how.
Let’s have our usual example of ‘firstname.lastname@example.org’ in cell A1. Your exercise is to make cell B1 read ‘www.workingdata.co.uk’.
We are trying to get all the information to the right of ‘@’, so first off let’s find it.
Putting our find equation in cell B1, gives us the answer five. And ‘@’ is indeed the fifth character in the cell counting from the left. However, we want to return only those characters to the right of ‘@’. That is, from position 6 onwards, so we need to add 1 to the result of our find function, in the formula bar, as below.
Now cell B1 gives us the answer six. We want to return the value ‘workingdata.co.uk’, and if you count in the cell from the left, then the ‘w’ character is indeed the sixth character. So far, so good.
Next bit is the tricky bit, we introduce the Mid function. You will remember that the Mid function requires three pieces of information to work;
- The cell to be investigated, in this case A1
- A starting position within the cell, in this case
(find(“@”,A1,1)+1)which equates to six in this example.
- The number of characters to display. We get this by subtracting the position of ‘@’ from the total number of characters in the cell, i.e.
(len(A1)-find(“@”,A1,1)). In our example this equates to seventeen, the number of characters in ‘workingdata.co.uk’.
So, in cell B1, if we now write our Mid function with each of these three elements in place,
and then hit return, B1 will now display ‘workingdata.co.uk’.
Nearly finished. All we need to do now is put a ‘www.’ on the front. For this, we go back to the concatenate function.
We modify the equation in cell B1 to read:
and we have our final result ‘www.workingdata.co.uk’.
You can now simply copy and paste the formula into the cell to the right of any email address to get the company website address, and thereby get all 4000 website addresses within minutes, rather than days! You don’t need to do one cell at a time, you can use the drag-down handle, or copy and paste down the entire column in one go. This combination of text functions has many applications which will save you days and weeks of mind-numbing, repetitive data entry. Try it out, and if you get stuck, just give Working Data a call and we’ll help you out!
We hope that you have found this month’s spreadsheet tips useful. If you have any questions or suggestions please do contact us.