Learn how to split data into different cells in Excel.
Split cell data
In some cases, the data found in a single cell must be separated into different cells before it can be imported into MindBridge.
In this example, we will split one cell to form both the Account Number and Account Description from the same cell.
- Begin by creating 2 new columns to the left of the data.
- Labeling each column:
- Account Number
- Account Description
- Split the account number using the formula below:
In this example, each Account Number is 5 digits long. This formula will copy the 5 leftmost characters of cell C6.
- Copy the Account Description using the MID function.
This function looks at the original data, counts 7 characters into the cell, and then copies the next 100 characters.
This formula will copy only the account description, and skip the account number:
=MID(C6, 7, 100)
- Select both formulas contained in columns A and B and copy them to the bottom using the Fill to Down function.
- Select cells A6 and B6, and scroll to the final row.
- Select the final row while holding down Shift on your keyboard.
- With both columns selected, go to the Editing tab in the Home ribbon and select Fill.
- Select Down from the menu options that appear.
Your dataset will now have the Account Numbers and Account Descriptions in separate columns, as shown below.
- Select columns A and B.
- Copy the contents of the 2 columns
- Paste as Values to remove the formulas.
- Save your file.
Anything else on your mind? Chat with us or submit a request for further assistance.