Suggest a VB.net Code to remove spaces in Column A of the excel

Suggest a VB.net Code to remove spaces in Column A of the excel. Using invoke code activity i want to implement this.

Hi @Gopi_Krishna1

Do you have data in excel or in a datatable. If possible prove an sample input.

Regards

I want to Delimit 24.02.2020 this value with . in Column A and paste this in B,C,D columns in B column i need 24 in C column i need 02 and in D Column I need 2020
like this

Hey @Gopi_Krishna1
try something like this:

For Each row As DataRow In YourDataTable.Rows
    If Not row("A") Is DBNull.Value AndAlso Not String.IsNullOrWhiteSpace(row("A").ToString()) Then
        Dim cleanedValue As String = row("A").ToString().Replace(" ", String.Empty)
        row("A") = cleanedValue

        Dim dateParts As String() = cleanedValue.Split("."c)
        If dateParts.Length = 3 Then
            row("B") = dateParts(0)
            row("C") = dateParts(1)
            row("D") = dateParts(2)
        End If
    End If
Next

Shall we keep this code in txt file and using Invoke VB code activity inside excel application scope can we impliment?

Hi @Gopi_Krishna1

Input:
image

Code:

Sub DelimitDate()
    Dim cell As Range
    Dim dateParts() As String
    
    ' Iterate through each cell in column A
    For Each cell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        ' Split the date value by '.'
        dateParts = Split(cell.Value, ".")
        
        ' Check if the dateParts array has three elements (day, month, year)
        If UBound(dateParts) = 2 Then
            ' Paste the values into columns B, C, and D respectively
            cell.Offset(0, 1).Value = dateParts(0) ' Day
            cell.Offset(0, 2).Value = dateParts(1) ' Month
            cell.Offset(0, 3).Value = dateParts(2) ' Year
        End If
    Next cell
End Sub

Paste the above code in a text file.

Workflow:

Text File Path:
DateSplit.txt (694 Bytes)

Output:
image

Hope it helps!!

You should use ‘invoke code’ activity in UiPath to use this code.
Excel Application Scope → Read Range → Invoke Code Activity.

Geart Its Working Properly, Thank you and also I need VB Script to add Header Name to Column A,B,C for example “Black”,“Blue”,“RED” like this

1 Like

Hi @Gopi_Krishna1

Please raise a new query. I will help you with that.

Regards

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.