• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:


I am copy and paste data from excel into a datagridview with the following code

Dim strPasteText As String = Clipboard.GetText
        Dim strSplitter() As String = {vbCrLf}
        Dim strSplitterC() As String = {vbTab}
        Dim strRows() As String = strPasteText.Split(strSplitter, StringSplitOptions.RemoveEmptyEntries)
        Dim iRows As Integer = strRows.Length
        Dim strCells() As String = strRows(0).Split(strSplitterC, StringSplitOptions.None)
        Dim iCells As Integer = strCells.Length
        DataGridView1.RowCount = iRows
        DataGridView1.ColumnCount = iCells
        For iColCounter As Integer = 0 To iCells - 1
            DataGridView1.Columns(iColCounter).Name = strCells(iColCounter)
        DataGridView1.Rows(0).Selected = True
        For iRowCounter As Integer = 1 To iRows - 1
            strCells = strRows(iRowCounter).Split(strSplitterC, StringSplitOptions.None)
            For iCellCounter As Integer = 0 To iCells - 1
                DataGridView1.Rows(iRowCounter - 1).Cells(iCellCounter).Value = strCells(iCellCounter)
        Me.DataGridView1.Columns(0).Width = 90
        Me.DataGridView1.Columns(1).Width = 55
        Me.DataGridView1.Columns(2).Width = 55
        Me.DataGridView1.Columns(3).Width = 55
        Me.DataGridView2.Columns(0).Width = 90
        Me.DataGridView2.Columns(1).Width = 55
        Me.DataGridView2.Columns(2).Width = 40

Open in new window

this works fine, however I do not have control of the excel data and sometimes it comes with some extra characters  that I do not want on the datagridview, since once I get the data on the datagridview I am writting to SQL table, so I need this data to be clean.

What I have seen on this data coming is that sometimes has extra spaces or characters like  -  or "  . so is there any way to eliminate  all this once I copy into the datagridview  or before I copy
 there are not a lot of data posible 100 rows and 4 columns
1 Solution
You can use trim function to get rid of any characters at either end of the string

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now