The string manipulation functions is our first article of the Excel productivity tools subject. In this series we will show you the most effective data cleansing techniques.
Before we would start this magnificent subject, allow us a little detour. We would like to summon an old memory from those times when I didn’t even know what Excel was.
After college studies I got employed at a small firm. As a beginner they didn’t dish out the premium tasks for me, but I got the seemingly very boring data cleansing task.
This wouldn’t have been a problem in its own, the trouble was that for this kind of analysis there was only a very short period of time. And I have never heard of the Excel string manipulation function, so the solution wasn’t simple; these tasks consumed many night shifts.
We would like that the text manipulation and Excel data processing would be a child’s play for you. Today we publish a universal Excel add-in tool and with its help the Excel string manipulation functions will be very simple!
String Manipulation Functions – Change Case
The first function is widely used with Excel text styled data. We can also use the classical Excel formulas but there are cases when this is just not enough.
On the following picture you can see what kind of Excel string manipulation functions we have built in.
A common information that is very important! As a first step always highlight the range that you would like to apply the text transformation to. We must start the program from the ribbon only after this!
From the left side list choose the Change Case element, then on the right side list will appear those options that you can use.
Of all the functions this requires the least explaining, in my opinion! After executing the operation the highlighted data range will look like as you can see it on the picture.
Add Text to manipulate the current String
With the use of some Excel functions we can resolve that we insert some arbitrary characters in front of or behind a given string.
The problem starts when we have to perform this operation on an Excel database.
By choosing the “Add Text” option we can manipulate a huge quantity of data (even several thousand lines) in a matter of seconds!
This task is virtually un-executable with the use of the simple Excel formulas.
From the Excel string manipulation formulas we have to emphasize the SEARCH(), FIND(), MID(), LEFT(), RIGHT() functions.
Now imagine that comfortable situation that you don’t have to create endless formulas ever again! Simply start the functions can be seen on the picture.
Text Manipulation Functions – Remove Characters
Out of the so far introduced text transformations some can be done with the use of previously described formulas.
The following will be a real treat for the professionals working in the field of business. Let’s stay a little more with the string manipulation functions. Opposite to the previous paragraph here we don’t insert the characters but remove them.
OK, at this point all people who good at Excel would say the following sentence: Let’s cut the characters from the front and back.
And what if we have to do this from the middle of the text or starting from the fifth character? Probably this is the moment when the average user would throw the keyboard from the desk…
Please try the remove by position function; it is a very effective working tool. Look through the list and you blood pressure will quickly be restored.
Remove Spaces to clean raw data
The next thing to do is to remove the spaces from the text. This is not complicated, simply can be done by using the Excel TRIM function.
In this case we’ve done nothing else then automatized this function. You don’t need to apply any formulas, just use the Remove Spaces and All Spaces command.
Remove the Special Characters from Text
Finally let’s see the special characters. We distinguish three different types.
The first on is the non-printable characters, the second is the alpha characters and the third one is the numerical data.
The tools can be seen on the picture are capable to remove only one type of character / characters from the text.
Imagine how useful can be this Excel application if we have to cleanse a database containing phone numbers. The cells have numbers and other characters also.
For example: +1-202-555-0111. Let’s say we need data that only can contain numbers. And the list of phone numbers five thousand rows.
What do you think how long would it take to remove the hyphens and other non-numerical characters? It takes too long merely thinking about it!
With the help of Excel string manipulation functions the time of execution narrows down to seconds.
Why should we use text manipulation functions?
Let’s summarize what have we learned today!
We have overviewed the most important text transformations and their field of use. The add-in that we are providing for you absolutely free of charge will take a lot of weight off your shoulders.
In case you would like to increase your knowledge of Excel visit the well-known Excel forums.
There is no shame in learning from the professionals; that’s what we have done in the early days. The Excel string manipulation function is a very large subject that definitely needs a lot of time and attention.
Concluison: Use Effective Data Cleansing Tools
We have a goal to make a complex add-in for you that will save you a lot of time. We have named it Excel productivity tool.
Presently we are at the early stages of development. We already know what kind of modules will be in the program. As you could see we have installed the text tools first.
This add-in is only the beginning in the next few days we would like to build in worksheet / workbook tricks. Among other things there also will be split, merge and compare tools.
We will continually develop the code so that the Excel supported data processing will gain more ground in the future. We welcome you back next week!
Update: Our new productivity Excel add-in has been released!