How to extract string from one column and place in another column

Please help I want to extract the year (last 4 characters from Date column) and place in Year column using UIpath. (Note: They are string values)
image

I’m trying to achieve this:
image

Pls help. Thank you.

Hi @gregoryoffodum

Welcome to Community!

Here below the steps to achieve the above!

Step1:Read Range the excel Using workbook activities
Step2 for each row of the read Datatable
Step3:Inside the body Use assign activity
StrArray(VariableType) = Split(CurrentRow(β€œDate”).toString,β€œ/”)
Step4: Assign Currentrow(β€œYear”)=StrArray(2)
Step5: Outside the body of the Loop write range with the same Read range path and Datatable.

And Please refer to xaml with the skeleton for your process.

Main.xaml (7.1 KB)

Regards

Hi @gregoryoffodum ,

If you want to achieve this with Linq then refer below steps:

1 - Read excel
2 - Use Linq to get year with respective dates
3 - Update in excel with write range.

Test.xaml (7.5 KB)
GetYearDateRows.xlsx (17.4 KB)

Output:

image

Hi @gregoryoffodum

We can do this in single line code itself

  1. Read the excel and store in dt1(check the preserve format option)

  2. Use invoke code and argument dt1 as in/out

dt1.AsEnumerable().ToList().ForEach(Sub(row) row("Year")= DateTime.ParseExact(row("Date").ToString,"MM/dd/yyyy", System.Globalization.CulturalInfo.InvariantCulture).Year.ToString))

  1. Write the dt1 to excel using write range

Regards

Nived N :robot:

1 Like

Thanks Calvin. Will try this. The field is already in string. No need for conversion

Hi @gregoryoffodum

Let me know if its work for you!

Regards

Thanks a lot. It does. But on a curious note, if the string were to be β€œ12/2/2019 00:00:00”.
what would the assigned array value be? I’m still interested in the year β€œ2019”

Hi @gregoryoffodum

If we assign the value string array to the date and split based on the / it will
Store based on index

String_Array(0) contains 12
String_Array(1) contains 2
String_Array(2) contains the remaining year with timestamp

To remove the timestamp use the replace condition as below

String_Array(2).Replace(β€œ00:00:00”,β€˜β€™")

Hope this clears!

Regards

yes it is now, thanks a lot :+1:

Hi @gregoryoffodum

Please mark it as solution and close the thread!

Regards

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