Split text to columns

In this post you can see how to split text to columns with:

First solution is better for small texts where you want to apply additional formating over the columns. The second one is for larger files where the data has the final format.

Split text to columns with excel

  • select the text that will be split
  • Click Data > Text to Columns .
  • Choose how to split:
    • Fixed size - when the text is strictly structured. By mouse click select which column to be used for split
    • Delimited - if there is a separator like : ",", ".", "|" or another character

  • Select all delimiters and how to treat consecutive once
  • Chose data types:
  • otherwise dates may be wrong
  • skip non needed columns
  • or just finish (if no additional format is needed)

Text splitting with Notepad++

Plugin TextFX

  • install the plugin via plugin manager

  • Menu > TextFX

  • Go toTextFX Edit

  • Click Line up multiple lines by (,) or any other depending on your needs

  • No you can select a column by block selection(check the section above)

block selection

  • open the file

  • press ALT and click left mouse button until the cursor cover all lines.

  • select a column by changing mouse position or

  • Now you can press Space or to write any other character to split the text in two with delimiter. For example "@" or delete whole columns:

Convert mails to columns by regex

For example split mails by notepad++: "@" is a special character for mails and also ".".

  • Menu Search > Replace(or simply CTRL + H)
  • Set Find what to "@"
  • Set Replace with to ";"
  • Enable Regular expression
  • Click Replace All

Related Article