Suggest a VB.net Code to remove spaces in Column A of the excel. Using invoke code activity i want to implement this.
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?
Input:
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:
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
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.