I love spreadsheets. I use them frequently in my job. But I never use them for doing things with numbers. Instead, I used the functions of Excel to take text apart – and consolidate it.
I deal with a lot of information, for my job, for my classes, and for Girl Scouts. When I get information in a form that is readable, but not quite usable, I have the option to re-type it…or I can put Excel on the task and let it do the work.
Most people assume that Excel, as a spreadsheet, is just for crunching numbers. Truth be told, I use Excel more for text manipulation than I ever do for numbers. Today I am going to show you how to consolidate text — and take it apart — using Excel.
Putting Text Together
When I have to put together any amount of text that is more than 20 lines long, or combines a bunch of copy/paste for many lines, I use Excel.
Putting text together in Excel is simply a matter of creating a formula to do it. The standard operator is the ampersand (&), which acts as a concatenation tool. Here are some examples on how to do this: (please show pictures to see the examples)
As you can see, you can add anything you want to the text string, expanding it, or putting other words in.
Taking Text Apart
The next part is how to take text apart. Far too often I receive information where the stuff I need is in the middle of blob of text. For instance, the spreadsheet I receive from my Girl Scout council with the leader training has the class titles mixed in with the date and the teacher. I just need the class title.
It’s easy to split text apart using Excel if you know how. This following example is a simplistic step-by-step demonstration on how to pull text apart.
First we start with the text we want to pull apart in a column. We examine the data and see that it is separated by commas. (Sometimes it will be separated by pipes (|) or tabs or spaces)
Next, we start the Text To Columns tool, located under the Data tab.
The first screen shows us the data, so we can see if it is delimited or fixed width (very little data these days is fixed width)
Next, we tell it what the delimiter is, and it shows us how the data will look:
The last screen is important…make sure to change the destination, or the split data will overwrite your original.
And you are done! Your data is now split apart.
Conclusion
Excel isn’t just for numbers. If you ever find yourself looking at having to retype a bunch of text because it isn’t in the form you need, check to see if you can use Excel!
Very good explanation on a very useful tip.
Thank you