Help with String manipulation - need to remove part of the string

I have a business need

  • to read and excel file with two cols. It is advisable to read these two columns by index and not be column name because these column header/name may change.

  • Once the excel file is read, the rows under column two ( index 1) need to be manipulated. We need to strip off the alphanumeric part before the first digit.
    So “before” below

before

Should be
after

  • Finally, Once the formatting is is complete, the two columns must be written to another excel file.

I have played with this using the REGEX and String.Split approaches ( see attached workflow) but I’m not getting the desired result.

I will appreciate some help because I’m still relatively new to UiPath. Thank you!

StripOffLeadingAlphaNumeric.zip (16.5 KB)

Hello

Take a look at this workflow I made for you Yomi.xaml (15.8 KB)

  • I have update the row values to your desired format
  • I have also included the column name changes (as per your screenshots).

Using this regex pattern to identify the text you want to remove combined with a for each row. See the below image of the Before (white) and After (orange).
imageimage

For Each row Instructions below:

Insert a ‘For Each Row’ where ‘row’ is your currentrow variable name.

Then use an assign like this:
Left Assign:
row(1)

Right Assign:
System.Text.RegularExpressions.Regex.Replace(row(1).tostring, “^[A-Z]0*”, “”)

You can learn more from my Regex Megapost

You can learn more about Datatables from this post:

Hopefully this helps :blush:

Cheers

Steve

1 Like

Hi @Yomi_Oluwadara

Try the below way:


Output:

After the Write Text file you can use Generate DataTable from Text activity to convert string to datatable and write that datatable to new excel.

Refer the updated zip file for better understanding
StripOffLeadingAlphaNumeric.zip (95.1 KB)

Hope it helps!!

1 Like

@Yomi_Oluwadara

use this expression in assign activity

dtExcel.AsEnumerable.Select(Function(a) dtExcel.clone.LoadDataRow({a(0).ToString,system.text.RegularExpressions.Regex.Replace(a(1).ToString,"K00","")},False)).CopyToDataTable

StripOffLeadingAlphaNumeric.zip (18.3 KB)

1 Like

@Yomi_Oluwadara

assign:
dt_Build=dt_Build.AsEnumerable.Select(function(x) dt_Build.Clone.Rows.Add(x(0).ToString,System.Text.RegularExpressions.Regex.Replace(x(1).ToString,“[a-zA-Z]0*”,“”))).copytodatatable

Cheers!!

1 Like

Hi @Yomi_Oluwadara

  1. Read Excel File using Read Range Activity with output variable DT as DataTable
  2. Take Assign Activity

DT= (From row In DT.AsEnumerable()
Let col2 = New String(row.Field(Of String)(1).Where(Function(c) Char.IsDigit(c)).ToArray())
Select DT.Clone().Rows.Add(row.Field(Of String)(0), col2)).CopyToDataTable()

Check below image for reference.

Hope you understand :slight_smile:
Cheers!!

1 Like

Hi @Yomi_Oluwadara

How about this

row(1) = System.Text.RegularExpressions.Regex.Match(row(1).ToString, “\d+.*”).Value

splitParts = row(1).ToString.Split("K"c)
row(1) = splitParts(splitParts.Length - 1)

1 Like

@Shiva_Nikhil @Parvathy @sanjay3 @Nawazish_Ahmad @sasi_poosarla @Steven_McKeering
Thank you all for the input. All the solutions and resources are so helpful.
Though there are several solutions here, I marked the simplest one as the final solution. Thanks all.

3 Likes

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