In some cases, the files we download from other applications does not come in a format that will enable us to analyse and present reports in Excel. For instance, if you copy information from a text file into excel all the data may appear in one column as shown below.
In this above situation, we have to first clean the data by splitting the information into different columns.
Proposed Solution
In many cases, you can eliminate the use of formulas and use Excel’s Text-To-Columns command to split strings into their parts. This command is found in the Data Tools group of the Data tab.
Here we select Data > Text to Columns > Delimited > Next.
- Select the list, then go to Data tools and Choose Text to Columns option
- Choose delimited
- Excel would propose a delimiter. Choose Space and click on Next option
- Click Finish
You can also learn using the Video below:
Comment
There are other options like MID, LEFT and RIGHT function. You can combine them with FIND and LEN function to create similar text splitting. However, the above method is faster particularly for less frequent events.
