Why would anybody want to automate slicing up cell contents in a spreadsheet? If you ever tried to copy a list from a web page or from a .txt file and paste it into Excel with the purpose of creating a database, then you know how frustrating it is to go through cells and edit them one by one. I’ll give you the first example that pops into my mind: you have a list of people, you have the name, surname and age of each person in a single cell, but you need to put them in a separate cells, in order to be able to order your table by surname or by age.
When importing .csv or .txt files into Microsoft Excel, you are presented the option to choose your field delimiters. Sometimes this is enough to get your information imported just the way you need it. However, field delimiters don’t offer much flexibility, so you need to further process Excel cells content in order to be able to use it. Such processing can be done by using Text functions. LEFT, RIGHT and MID are the three functions which, in combination with a few others, will save you heaps of time. To show you how to do it, I’ll use the previous example with the list of names. I’ll first detail on each function used, then I’ll show you how to combine functions.
This is the initial spreadsheet:
We need our one column table to be split in three columns, for name, surname and age. By using the LEFT function we can “tell” Excel to return a certain number of characters only, so we can obtain a new column containing only names.
The syntax is LEFT(text,num_chars). This means that we need to indicate two parameters:
When importing .csv or .txt files into Microsoft Excel, you are presented the option to choose your field delimiters. Sometimes this is enough to get your information imported just the way you need it. However, field delimiters don’t offer much flexibility, so you need to further process Excel cells content in order to be able to use it. Such processing can be done by using Text functions. LEFT, RIGHT and MID are the three functions which, in combination with a few others, will save you heaps of time. To show you how to do it, I’ll use the previous example with the list of names. I’ll first detail on each function used, then I’ll show you how to combine functions.
This is the initial spreadsheet:
We need our one column table to be split in three columns, for name, surname and age. By using the LEFT function we can “tell” Excel to return a certain number of characters only, so we can obtain a new column containing only names.
The syntax is LEFT(text,num_chars). This means that we need to indicate two parameters:
- the text we need to operate the cut on
- the number of characters that will be cut and pasted into the new cell
Do you see where the problem is? Not all names on the list have the same number of characters, so we can’t just write the formula for the first cell, then drag it down onto the whole table. We need to enter a different number for each line, and that’s not at all a time saver. What if we found a way to automatically count the number of characters in each name, then use this variable in our LEFT formula, so it cuts the proper number of characters for each line?
There’s a function called FIND, which counts the number of characters in a text starting from a certain position to a special character you indicate. The special character in our case is the “space” between name and surname.
Function syntax: FIND(find_text; text; position)
Parameters:
find_text = your special character (in this case it’s “space”)
text = the initial text string (here you’ll want to put its cell reference, so you can then drag your formula down across the whole table)
position = the position you need to start counting from (this parameter is optional, the formula will work even if you leave it out).
There’s a function called FIND, which counts the number of characters in a text starting from a certain position to a special character you indicate. The special character in our case is the “space” between name and surname.
Function syntax: FIND(find_text; text; position)
Parameters:
find_text = your special character (in this case it’s “space”)
text = the initial text string (here you’ll want to put its cell reference, so you can then drag your formula down across the whole table)
position = the position you need to start counting from (this parameter is optional, the formula will work even if you leave it out).
Here’s my table again, but this time I added the following columns
= FIND(” “;A2) – this is to determine how many characters are there in the first name (it will also count the space, so you’ll need to subtract it in the next formula) – we just count the characters to the left of the space
= FIND(” “; A2; B2+1) – this is to count the number of characters from the beginning to the second space in the text (I used B2+1 as starting position, so the formula ignores the first space)
= LEN(A2) – this counts the total number of characters in the text string. We need it for obtaining the age column with the help of the RIGHT function
Then we use the three aids we just defined to obtain our final columns:
Name: =LEFT(A2;FIND(” “;A2)-1)
Surname: =MID(A2;B2;C2-B2)
Age: =RIGHT(A2;D2-C2)
Now that we know how to do it, why not combine the formulas so we skip the aid columns? All you need to do is replace B2, C2 and D2 with their corresponding formulas, drag the formulas all way down the table and you’re done:
Name: =LEFT(A2;FIND(” “;A2)-1)
Surname: =MID(A2;FIND(” “;A2);FIND(” “; A2; FIND(” “;A2)+1)-FIND(” “;A2))
Age: =RIGHT(A2;LEN(A2)-FIND(” “; A2; FIND(” “;A2)+1))
This is how you can count characters to the left of a specific character in a text, so you can cut the text where you need. I hope it will help some of you out of trouble. It works in MS Excel, but also in OpenOffice Calc (which is what I used for this tutorial). Please let me know if you used this formula combination and how it worked for you.
= FIND(” “; A2; B2+1) – this is to count the number of characters from the beginning to the second space in the text (I used B2+1 as starting position, so the formula ignores the first space)
= LEN(A2) – this counts the total number of characters in the text string. We need it for obtaining the age column with the help of the RIGHT function
Then we use the three aids we just defined to obtain our final columns:
Name: =LEFT(A2;FIND(” “;A2)-1)
Surname: =MID(A2;B2;C2-B2)
Age: =RIGHT(A2;D2-C2)
Now that we know how to do it, why not combine the formulas so we skip the aid columns? All you need to do is replace B2, C2 and D2 with their corresponding formulas, drag the formulas all way down the table and you’re done:
Name: =LEFT(A2;FIND(” “;A2)-1)
Surname: =MID(A2;FIND(” “;A2);FIND(” “; A2; FIND(” “;A2)+1)-FIND(” “;A2))
Age: =RIGHT(A2;LEN(A2)-FIND(” “; A2; FIND(” “;A2)+1))
This is how you can count characters to the left of a specific character in a text, so you can cut the text where you need. I hope it will help some of you out of trouble. It works in MS Excel, but also in OpenOffice Calc (which is what I used for this tutorial). Please let me know if you used this formula combination and how it worked for you.
No comments:
Post a Comment