That old adage about not being able to see the forest for the trees is all so true. At least it was for me all this time I have been using the Text to Columns feature in Excel! I have been using this feature for a very long time as it is a very robust feature, as you well know if you import data from an other applications into Excel. As it turns out, it also can be used in other ways.
Not only do I write a newsletter, but I also subscribe to many other newsletters (thankfully) because nobody knows everything, and it allows me to learn and broaden my horizons. And so it is that I have finally learned about a feature in Text to Columns that has eluded me all this time.
When you split a column of data using the Text to Column Feature, your data is split, starting with the column holding the original data. It is then distributed across columns on the right side. Hence, there a re a couple of things to keep in mind here:
- Your original data will disappear
- Columns to the right must be empty
In the past, when I wanted to save my original data, or if there were not any empty columns to the right, I would copy my data and put it in another empty part of my worksheet or put it in a new worksheet.
And I, the queen of saving time and keystrokes, absolutely did not need to do that! I have now learned from Francis Hayes in an article he wrote, that I did not need to do that at all!
This is the part where I could not see the forest for the trees!
In Step 3 of 3 in the Text to Column Wizard, there is a destination field and it gives you the choice of selecting a different cell where the results of your Split will go and it then preserves your original data!
Wow thanks Francis! I am so glad to have learned this and that I can pass it on to others!