Remove Duplicate digit from cell

Help Studio
, ,
1

Hello,
I have a column in excel as below.
Roll Number
1123456789
1111234567
2345117859
2311451178
234567898

I want to remove duplicate 1’s from each cell. It should remove duplicate digit (1) only from cell which it finds in pair.

Output as below:
New Roll Number
123456789
11234567
234517859
23145178
234567898

Any help or xaml will be appreciated.

Regards,
Zahid Akhtar

2

Hi @Zahid_Akhtar

Try this:

dt_Output = (From row In dt.AsEnumerable()
 Let rollNumber = String.Join("", row.Field(Of String)("Roll Number").Select(Function(c, i) If(i < row.Field(Of String)("Roll Number").Length - 1 AndAlso c = "1" AndAlso row.Field(Of String)("Roll Number")(i + 1) = "1", "", c)))
 Select dt.Clone().Rows.Add({rollNumber})).CopyToDataTable()

Hope it helps!!

3

Hey @Zahid_Akhtar
try this way:

  1. Read Range to data table eg. dtRollNumbers
  2. For Each Row (dtRollNumbers)
    Assign to: CurrentRow("Roll Number")= System.Text.RegularExpressions.Regex.Replace(row(“Roll Number”).ToString, “11”, “1”)`)
  3. Write Range
4

@Zahid_Akhtar

(From row In YourDataTable.AsEnumerable()
                 Let originalRollNumber = CStr(row.Field(Of Double)("Roll Number"))
                 Let newRollNumber = String.Concat(Enumerable.Range(0, originalRollNumber.Length - 1).
                                                    Where(Function(i) originalRollNumber(i) <> "1" OrElse originalRollNumber(i + 1) <> "1").
                                                    Select(Function(i) originalRollNumber(i)))
                 Select YourDataTable.Clone().Rows.Add({newRollNumber})).CopyToDataTable()
5

Hi @Zahid_Akhtar

→ Build Data Table
image
Output-> dt
→ Use the below syntax Assign:

dt_Output = (From row In dt.AsEnumerable()
 Let rollNumber = String.Join("", row.Field(Of String)("Roll Number").Select(Function(c, i) If(i < row.Field(Of String)("Roll Number").Length - 1 AndAlso c = "1" AndAlso row.Field(Of String)("Roll Number")(i + 1) = "1", "", c)))
 Select dt.Clone().Rows.Add({rollNumber})).CopyToDataTable()

image
image1014×675 24.7 KB

Sequence82.xaml (11.8 KB)

Hope it helps!!

6

Thanks a lot guys for timely help. I tried solutions from @pikorpa and @Parvathy and both of them worked for me :slight_smile:

1 Like
7

You’re welcome @Zahid_Akhtar

Happy Automation!!

8

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