Questions? We have answers.

Separating cell data in Excel

  • Updated

Summary

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 the Account Number and Account Description from the same cell.

image_-_2021-01-07T124215.256.png
 
  1. Begin by creating 2 new columns to the left of the data.
  2. Labeling each column:
    • Account Number
    • Account Descriptionimage_-_2021-01-07T124231.213.png
  3. Split the account number using the formula below:
    =LEFT(C6,5)

    In this example, each Account Number is 5 digits long. This formula will copy the 5 leftmost characters of cell C6.

    image_-_2021-01-07T124248.389.png
  4. 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)
    image_-_2021-01-07T124305.220.png
  5. Select both formulas contained in columns A and B and copy them to the bottom using the Fill to Down function.
  6. Select cells A6 and B6, and scroll to the final row.
  7. Select the final row while holding down Shift on your keyboard.
  8. With both columns selected, go to the Editing tab in the Home ribbon and select Fill.
  9. Select Down from the menu options that appear.
    image_-_2021-01-07T124321.800.png

    Your dataset will now have the Account Numbers and Account Descriptions in separate columns, as shown below.

    image_-_2021-01-07T124338.310.png 

  10. Select columns A and B.
  11. Cut the contents of the 2 columns
  12. Paste as Values to remove the formulas.
    image_-_2021-01-07T124352.697.png
  13. Save your file.


Anything else on your mind?

Head over to the community with your thoughts and inquiries! You can also chat with us, or, submit a request for further assistance.


Related articles

Was this article helpful?