Skip to main content Skip to footer

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

Splitting text into columns is a useful feature in Excel that does not get the attention it deserves. This feature, as the name suggests, allows users to separate text into multiple columns by comma, space, or another delimiter. This is generally used when analyzing data from an external source where all the information is exported into one column. For example, converting invalid date formats to a valid format, splitting a name column into first and last name columns, getting the root domain from a URL…The list goes on and on. However, when relying on a standalone spreadsheet software, like Excel, changes to the data may go unmonitored and un-updated, leading to the possibility of outdated or incorrect analysis.

Many developers are using spreadsheet components, APIs, and libraries to proactively organize imported data and even offer dialogs for their users to split data in their applications during runtime – removing their need for an isolated Excel file. In this article, I will use GrapeCity’s Spread.NET, the world's #1 selling .NET spreadsheet component to demonstrate how to programmatically split text to columns using C# and VB.NET, how to invoke an Excel-like Columns Wizard during runtime for end users, and how to implement the Text to Columns feature in Spread's code-free designer.

This Article Covers

Programmatically Split Excel Data into Columns using C# or VB.NET

As a .NET developer using Spread.NET, you have the advantage of programmatically automating many of the manual processes that end-users typically perform in Excel. By programmatically invoking the Text to Columns feature or using Spreads' built-in TEXTSPLIT function, you can simplify and streamline data processing for your users. This not only saves time but also ensures that the data is organized and structured consistently every time. Also, by writing code to perform these actions, you can add an extra layer of control and security to sensitive data, reducing the risk of human error.

In this code snippet, you can see how the Text to Columns method can be used to split text into separate columns using a suitable 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:

Using Spread.NET's Text to Columns method in C# code

Below you can see how to place a formula, the TEXTSPLIT function, in a cell using C#/VB.NET. This functionally is equivalent to using the Text to Columns method to split the text, by operating on rows and specifying custom delimiters.

C#:

IWorkbook workbook = fpSpread1.AsWorkbook();
workbook.WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.All;

fpSpread1.ActiveSheet.Cells["A1"].Value = "Data";
fpSpread1.ActiveSheet.Cells["A2"].Value = "Dakota Lennon Sanchez";
fpSpread1.ActiveSheet.Cells["A3"].Value = "To be or not to be";
fpSpread1.ActiveSheet.Cells["A4"].Value = "1,2,3;4,5,6";
            
fpSpread1.ActiveSheet.Cells["C1"].Value = "Formulas";
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("A1").Value = "Data"
fpSpread1.ActiveSheet.Cells("A2").Value = "Dakota Lennon Sanchez"
fpSpread1.ActiveSheet.Cells("A3").Value = "To be or not to be"
fpSpread1.ActiveSheet.Cells("A4").Value = "1,2,3;4,5,6"
    
fpSpread1.ActiveSheet.Cells("C1").Value = "Formulas"
fpSpread1.ActiveSheet.Cells("C2").Formula = "TEXTSPLIT(A2,"" "")"
fpSpread1.ActiveSheet.Cells("C3").Formula = "TEXTSPLIT(A3,"" "")"
fpSpread1.ActiveSheet.Cells("C4").Formula = "TEXTSPLIT(A4,{"","","";""})"

Splitting Text by Column in .NET Applications

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

Check out the Spread.NET Demo Explorer to learn more about the TEXTSPLIT Function.

Invoke Runtime Excel-like Dialogs for End-users to Split Text to Columns within .NET Apps

New with the v16 release, Spread.NET now offers a powerful built-in Excel-like Convert Text to Column Wizard dialog. This dialog can be invoked programmatically and provides end users with an interactive UI during the .NET apps runtime, so they can parse the text from one cell or column into multiple columns using a delimiter. The BuiltInDialogs class provides the TextToColumns method to invoke this dialog at, making it possible for users to parse data and convert it into the desired format manually during runtime.

Below is a code snippet showing how to invoke the Convert Text to Column Wizard.

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:

Invoke a TextToColumns Wizard Dialog in during development and a .NET Apps Runtime

Split Text to Columns Using the Spread Designer

Spread.NET offers a Designer that allows developers to visually design a Spread component using a graphical Excel-like user interface and also supports being deployed during runtime to allow their users to modify spreadsheets in their application. The Spread Designer has many advanced features with a familiar Excel-like look and feel. This Designer saves developers time by removing the need to programmatically create and or modify the Spread component.

Spread Designer provides a Text to Columns ribbon button in the Data Tools group of the Data tab.

Spread.NET's Designer Ribbon - Data Tab

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

In the below GIF, the Spread Designer is being accessed through Visual Studio 2022 during design time in a WinForms .NET app. Notice that the changes applied in the Spread Designer using the Convert Text to Columns Wizard are observed during the app's runtime.

Split text using a .NET Spreadsheet Designer

GrapeCity Spreadsheet Components

This article does not even scratch the surface of the full capabilities of the GrapeCity Spread.NET spreadsheet component. Download a free 30-day trial, review the documentation to see some of the many available features, and download the 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 with the v16 release, check out our release blog.

comments powered by Disqus