My last year at university one of my professors gave us a side lecture about some useful excel tips we will use once we start working. I don’t remember all of the tips that he gave us but I do remember that he taught us about ‘text to columns.’ He said that we would use this function many times for sure. I was somewhat familiar with that function but I could not think of any practical uses for it.
It wasn’t even two months in to my first job that I became a text to columns expert. I found out that many companies are still using a dos based (greenscreen) system. This means that data would be downloaded in a printable format that is not excel friendly. So you may be copying and pasting from a dos screen into excel for hours. I quickly learned that the best way to deal with this is to download the text file, import it into excel, and then use text to columns.
The basic function of text to columns is to separate items that are in just one column so that they can be manipulated. For example if you have data that has separate columns that are separated only by a certain number of spaces then your information in text format would look very awkward:
Title 1 Title 2 Title 3 Title 4
One Two Three Four
Apple Bear Captain Dolphin
TeamNumberOne TeamNumberTwo TeamNumberThree TeamNumberFour
In this example you can copy all this text directly into column A in excel. Then click on the text to columns button and separate the text into columns every 5 spaces. Then you can manipulate this data anyway that you wish. You can put it into a table and sort and filter.
This is especially useful when you work for a company that has an older IBM system or greenscreen set up. But there is another use for text to columns that I have discovered. Sometimes when you have raw data that is in text format and you want it to be in number format, you can right click and change the format to number. But this doesn’t always give you what you want. If you highlight the column and do text to columns, but don’t separate it into any specific columns, it will change the format for you.
Accountants are usually the best at using excel from what I have seen. At one of the companies I worked for, I became known as the local excel expert even though I knew relatively little. I think it’s really all about searching the internet for help on how to perform a certain task and then building a library of useful tasks. Several times during closing I would be asked by a high up manager to come to their office. Once inside I would be asked to help them with a vlookup formula or how to sort a table. I remember always wondering how these high up smart managers couldn’t figure out such simple tasks.
Once I told an older co-worker to “just play around with it till you figure it out.” He told me that was how young people talk. I asked him what he meant and he responded that young people know how to just mess around on a computer until they learn how to do things but older people need to read or ask for help to learn. That was good advice to me as I learned to be a good business support member for the company.
Anyway, it’s always good to keep your excel skills up to par as an accountant. It’s not often written on the job descriptions but when you get into an interview and explain that you are proficient with excel it is often a good high point.