How do I split one Excel column into multiple columns?
  • 02 Feb 2023
  • Contributors
  • Dark
    Light
  • PDF

How do I split one Excel column into multiple columns?

  • Dark
    Light
  • PDF

Article summary

You can use the Text to Columns feature in Excel to split one column into multiple.

Answer:
The Text to Columns feature in Excel allows you to split one Excel column into multiple columns.  The splitting can be based on the location or on a field delimiter.  If you have a column with "Mary Smith", you can split it on a delimiter of a space.  If you have "00DPUadam", and you want to split out the Fund Id of adam, you would base the split on the location of the text - in this case between the 5th and 6th characters.  Hopefully one of the two methods will allow your column to be split appropriately.
  • In Excel this option is located under Data. 
  • Highlight your field then click the option and choose Delimited if you will base the split on a delimiter (e.g. a space) or choose Fixed Width if you will based the split on the location of the text. 
  • On the next screen either choose the Delimiter (typically a space or comma), or if you picked Fixed Width, move the vertical bars around to split the column where you want it. 
  • Click to add additional breaks. 
  • When you are satisfied with the split, click Next, and then click Finish.
Some other uses for this utility are:
  • Splitting the dollar value from the "-" sign in a Trial Balance Report
  • Parsing the Fund Id from the full GL Account Key if working with a Journal History Report
  • Separating the Fund codes from the Fund ID in the Fund Balance Report
  • Parsing a name into the name parts
  • Splitting an address into separate elements

Was this article helpful?