How to Split Text to Columns in Excel XLSX using C# and VB.NET

When dealing with Excel data in .NET applications, it's common to encounter text that needs to be split into multiple columns. In this guide, we’ll show how to split text to columns programmatically using an API or by invoking a runtime Convert Text to Columns Wizard using the .NET spreadsheet component, Spread.NET, in C# or VB.NET, ensuring you and your users can efficiently manage data within your WinForms applications.

Start Using Spread.NET Today! Download a Free Trial

Split Text to Columns Using C#/VB.NET in .NET WinForms Apps

1. Programmatically Split Text into Columns in C# and VB.NET
• TextToColumns Method
• TEXTSPLIT Function
2. Invoke the Text to Columns Wizard in WinForms Apps

1. Programmatically Invoking the Convert Text to Columns Wizard
2. No-Code Solution with Spread Designer and Ribbon Control

Programmatically Split Text into Columns in C# and VB.NET

To automate the "Text to Columns" functionality in a .NET application, developers can use Spread.NET's API to invoke the TextToColumns method or use the built-in TEXTSPLIT function and efficiently split text in cells, similar to Excel's built-in feature. This approach ensures consistent data organization while reducing the risk of manual errors.

TextToColumns Method

The following code demonstrates invoking the TextToColumns method to split text into separate columns using a delimiter or column width option.

C#:

private void OnTextToColumnByCodeClick(object sender, EventArgs e)
  {
      var sheet = _fpSpread.AsWorkbook().ActiveSheet;
      var selection = sheet.Selection;
      sheet.Cells[selection.Row, selection.Column, selection.Row2, selection.Column2]
          .TextToColumns("$B$1", TextParsingType.Delimited, TextQualifier.DoubleQuote, false, false, false, true);
  }

VB.NET:

Private Sub OnTextToColumnByCodeClick(ByVal sender As Object, ByVal e As EventArgs)
    Dim sheet = _fpSpread.AsWorkbook().ActiveSheet
    Dim selection = sheet.Selection
    sheet.Cells(selection.Row, selection.Column, selection.Row2, selection.Column2).TextToColumns("$B$1", TextParsingType.Delimited, TextQualifier.DoubleQuote, False, False, False, True)
End Sub

Download the Spread.NET WinForms Demo Explorer and check out the Worksheet’s Text to Column demo to learn more:

TEXTSPLIT Function

The following code shows how to use the TEXTSPLIT function in a cell with C# or VB.NET. This is equivalent to the TextToColumns method for splitting text, operating on rows with custom delimiters.

C#:

IWorkbook workbook = fpSpread1.AsWorkbook();
workbook.WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.All;
...
fpSpread1.ActiveSheet.Cells["C2"].Formula = "TEXTSPLIT(A2,\" \")";
fpSpread1.ActiveSheet.Cells["C3"].Formula = "TEXTSPLIT(A3,\" \")";
fpSpread1.ActiveSheet.Cells["C4"].Formula = "TEXTSPLIT(A4,{\",\",\";\"})";

VB.NET:

Dim workbook As IWorkbook = fpSpread1.AsWorkbook()
workbook.WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.All
...
fpSpread1.ActiveSheet.Cells("C2").Formula = "TEXTSPLIT(A2,"" "")"
fpSpread1.ActiveSheet.Cells("C3").Formula = "TEXTSPLIT(A3,"" "")"
fpSpread1.ActiveSheet.Cells("C4").Formula = "TEXTSPLIT(A4,{"","","";""})"

Download the Spread.NET WinForms Demo Explorer to explore the Calculation’s Text and Array Functions demo and learn more about the TEXTSPLIT function.

Invoke the Text to Columns Wizard in WinForms Apps

Spread.NET provides multiple ways for users to access the powerful built-in Excel-like Convert Text to Column Wizard dialog. You can either invoke the wizard programmatically using the BuiltInDialogs classor let users access it through the Spread Designer or the Ribbon Control during runtime with no extra code required.

Programmatically Invoking the Convert Text to Columns Wizard

The BuiltInDialogs class allows you to call TextToColums to invoke the built-in Excel-like Convert Text to Columns Wizard within your .NET WinForms app. This enables users to manually parse and format data during runtime.

C#:

private void OnTextToColumnMenuItemClick(object sender, EventArgs e)
  {
    var textToColumnDialog = FarPoint.Win.Spread.Dialogs.BuiltInDialogs.TextToColumns(_fpSpread);
    if (textToColumnDialog != null)
        textToColumnDialog.Show(_fpSpread);
  }

VB.NET:

Private Sub OnTextToColumnMenuItemClick(ByVal sender As Object, ByVal e As EventArgs)
    Dim textToColumnDialog = FarPoint.Win.Spread.Dialogs.BuiltInDialogs.TextToColumns(_fpSpread)
    If textToColumnDialog IsNot Nothing Then textToColumnDialog.Show(_fpSpread)
End Sub

Download the Spread.NET WinForms Demo Explorer and check out the Worksheet’s Text to Column demo to learn more.

No-Code Solution with Spread Designer and Ribbon Control

Spread.NET also includes a Spread Designer and a Ribbon Control, which provide users with a simple, code-free way to access the Text to Columns functionality. These tools feature a Text to Columns button in the Data Tools tab, allowing users to split text directly within the application at runtime.

Download a trial of Spread.NET to check out the Spread Designer and Ribbon Control!

The below depicts the C# .NET Ribbon Control invoking the Text to Columns Wizard during the app’s runtime.

Both approaches enable a seamless experience, whether you're automating the process programmatically or giving users control via a familiar UI.

Learn More About C# .NET Spreadsheet Components

This article only scratches the surface of the full capabilities of Spread.NET, the .NET C# spreadsheet component. Review the documentation to see some of the many available features, and download our demo explorer to see the features in action and interact with the sample code. Integrating a spreadsheet component into your applications allows you to customize your users' experience and provide them with familiar spreadsheet functionality without referring them to an external program. To learn more about Spread.NET and the new features added in the latest release, check out our release pages.