How to Use Text to Column Function?
In the previous edition, we focused on Text function and gave a more practical example. Today, Text To Columns function is used to separate the contents of one Excel cell into separate columns. For example, when you want to separate a list of full names into last and first names. Or, when you upload the numbers from PDF to Excel, sometimes they end up in one column.
Text to Column – Alt A E
To find “Text To Column” function on the Ribbon, you’d need to press ‘Alt’ key first (to access the Ribbon), then ‘A’ for “Data” and then ‘E’.
Excel will open “Convert Text To Columns Wizard” window and automatically separate the information into columns. If you are happy with that, click “Next” by pushing ‘Alt + N’ at the same time, as ‘N’ is highlighted on the “Next” button.
‘Alt + F’ to “Finish” or ‘Enter’ for OK, when you are happy with the result.
You can also choose Delimeters, such as Semicolon, Comma, Space or others.
Sometimes numbers are uploaded from PDF to Excel as text, and changing the format doesn’t help.
Here’s a little Excel trick for you: if you multiply those numbers by 1, that would convert them to numbers.
Can you imagine how much time does it take manually separate the large data from one column into several? Trust me, I’ve done that before I learned about “Convert Text To Columns Wizard”, sometimes it takes hours. Don’t do the same mistake that I did and use Excel tricks to save your time and spend it with your friends and family.
Learn more Excel tips and tricks in our keySkillset.