Excel Tips: String skills – part 1
There are MANY things that you can do in excel. These can be split into one of two groups:
1. Sheet formulae – These are formulae that are entered into cells on the worksheet.
2. Macros – These are created in the visual basic editor and are run in the background, where you can only see the output of the macro.
I will start my explanation of sheet formulae with string skills.
String Skills – Part 1: Finding text
Most of the data that you will work with inside excel will be saved as either string, datetime or integer values. The issues usually occur when you want to use tools specific to a certain format that do not work with the string (also known as the general format). I will go into how to convert the string format into other formats at a later date.
The first skill to learn when dealing with strings is learning how to separate strings so that you can use them. Usually, when you download a string, it will be a large collection of text which most likely will be a) hard to read and understand and b) difficult to work with. You could go through each cell and modify the text as you require, but this is slow and laborious and it’s quite easy to make a mistake. Below are some ways to quickly sort through strings of text.
1. Finding text in a string
To find a particular piece of text within another string (to find a string within a string), you can use the FIND function. The syntax for the FIND function works as follows:
=FIND(what text you’re trying to find, where you are looking for this text, where inside the string you want to start)
Here’s an example. Say you have the following string:
searchforthewordoctopusinthislengthoftext
As you can see, there are no spaces in this string. You need to see if the word “octopus” is contained within the string.
To search for “octopus” in the string use the following code:
=FIND(“octopus”,A7,1)
This will then return the value of the first character in the string.
This shows you how far into the sentence the letter “o” is. You can then use this, either to extract the word out of the string, or cut the string into pieces…there are many things you can do!
What happens if it cannot find the character/word you are looking for? Say you searched for the word “car” in that string. As there is no “car” contained within the string, it will return the error #VALUE! This is not a pretty way to see errors, as this generic error code can represent a multiple of different errors. The best way to deal with this is to handle the case where it does not find what you are searching for.
To do this, you can use the ISERROR function.
The ISERROR function tests the output of a function and if it is an error, you can tell excel what you wish the output to be. So to put this into practice…(see my post on the correct use of practice vs practise…)
You can use the formula below:
=IF(ISERROR(FIND(“car”,A7,1)),0,FIND(“car”,A7,1))
I will explain the IF function in detail later, but just for this case, the IF function is basically a decision-making tool. You test a situation, and if it’s true, excel will perform one action and if it’s false, it will perform another. In this case, IT IS an error, and so it will return the value of zero.
So, looking at the code, this is what it says:
Search cell A7 for the string “car”. If it cannot be found, then return the value of 0, otherwise, return the value of the letter c.
This is similar to just using the FIND function, however, it will avoid nasty errors.
Hopefully you have found this useful; stay tuned for String skills – part 2 : The LEFT, RIGHT and MID functions.
Come check out the directory for the rest of my excel tips!
Submeg
IT
SUBMEG 