Copy data from one column and paste in another column with last five digit

[Sonam_N]

Hi Guys,

I have a excel with column A. In this i have multiple rows with different values ABCD43423 like that. I need last 5 digit from that A column and write into another B column for each row.

I am using for each row its working but taking time. Any another approach i can use. plz help

[rlgandu (Rajyalakshmi Gandu)]

@Sonam_N

DT = (From row In DT.AsEnumerable()
      Let lastFive = row.Field(Of String)("A").Substring(row.Field(Of String)("A").Length - 5)
      Select row.Item("B") = lastFive, row).CopyToDataTable()

Read Range the workbook store it in datatable
Assign activity
Write Range Workbook

Hope it works for you

[Parvathy (PS Parvathy)]

Hi @Sonam_N

=> Read Range Workbook
Output → dtData

=> Use below LINQ expression in Assign activity:

dtData = (From row In dtData.AsEnumerable()
          Let newValue = If(row("ColumnA").ToString.Length >= 5, row("ColumnA").ToString.Substring(row("ColumnA").ToString.Length - 5), row("ColumnA").ToString)
          Select dtData.LoadDataRow({row("ColumnA"), newValue}, False)).CopyToDataTable()

=> Write Range Workbook dtData back to excel.

Hope it helps!!

[lrtetala (Lakshman Reddy)]

Hi @Sonam_N

Can you try the below

Code:

For Each row As DataRow In dt.Rows
    Dim originalValue As String = row("Values").ToString()
    If originalValue.Length >= 5 Then
        row("Digits") = originalValue.Substring(originalValue.Length - 5)
    Else
        row("Digits") = originalValue 
    End If
Next

Input:

Output:

Regards,

[Sonam_N]

@lrtetala

Thanks for Your help