Option 1: Column separation with numbers
Let's start with the simplest embodiment of columns in Excel, the values of which contain certain numbers. In our case, these are thousands and hundreds, separated by a comma - this is visible in the following screenshot.
The task is to divide thousands and hundreds into different columns, which may be needed with further calculations of these amounts. Here you can quite do without creating cunning formulas, contacting the tool built into the program.
- Select the column you want to divide, and then go to the Data tab.
- Click on the "Column Text" button. Yes, even though the tool is related to the text, it will not prevent anything to use it for money sums, dates or other numbers.
- The "Column Text Distribution Wizards" window appears, in which you select the option "with separators" and go to the next step.
- As a separator symbol, specify that sign that is used in the column. If it is impossible to mark it with a check mark, activate the "Other" option and independently place this symbol.
- In the Sample Data Sample Block, see how the columns look like after separation.
- Data format Leave the total or set the date for it if it comes to it.
- By default, the new column is placed in the neighbor, but you can independently choose a position for it.
- Manually write the area or mark it on the table.
- Once the distribution actions are completed, click "Finish" to apply new settings.
- Returning to the table, you will see that everything is done correctly. It will be left to make minor adjustments - for example, continue separation or form formulas for the table.
- We will specify that there are no differences in the cell format, and from you when executing this option you only need to correctly indicate the separator symbol and the area where you want to place a new column. All other actions are carried out automatically.
If you have a table with dynamically variable numbers you want to regularly divide on the columns, read the instructions from the following option, which describes the creation of the formula when dividing text. It is suitable for numbers, only the conditions will have to edit a little for themselves.
Option 2: Text Separation
To split text on columns, the same rules apply, but there is a second option - creating a complex formula that will create two or more columns with data and will fill them automatically when editing. This is a difficult task, to deal with which it is necessary in stages, which is also dedicated to another article.
Read more: Separation of text in Microsoft Excel