What could be more efficient way to Substring ~15.000 rows in excel?

Hello everyone,

I have an excel file with ~15.000 rows that need to be cut(Cut means to get last 8 characters from each row).
Currently, I’m using Substring method but that takes too much time(around 20 minutes).

Is there any faster approach to solve this problem?

Thanks in advance.


Hope this thread would help you resolve this

Cheers @bp777

@bp777 - if you could give us a sample row value and value you needed in the new column we would take a look.

Of course.

Original value: 14218PP80907576
I need to get 80907576

@bp777 - To get last 8 you can use Right Function …see the example below…


and use this below code in the invoke code…

dt.AsEnumerable().ToList().ForEach(Sub(row) row("NewColumnName")=Right(row("ExistingColumnName").ToString,8))
1 Like

@bp777 - Here is the sample…

  1. Read Range - and called my datatable as dt.

  2. Add Add DataColumn -

  3. Invoke Code

dt.AsEnumerable().ToList().ForEach(Sub(row) row("substring") = Right(row("Org Value").ToString,8))
  1. Write Range your output

Here is my Output


Hope this helps…

1 Like

We can do it without Loops and LINQs by following (Condition: Cols with text is of datatype: String)

Add a dataColumn Name: lgth, DataType: Int32
Add a dataColumn Name: Extract, DataType: String

Modify the Expressions for the added data cols:

dtData.Columns("lgth").Expression = "LEN([Column1])"
dtData.Columns("Extract").Expression = "SUBSTRING([Column1],1,[lgth] - 1)"

Find starter Help:
DCExpression_GetLastXChars.xaml (7.6 KB)

Wow, that’s a very fast way!

I have one more question - is there a way to do the same thing on already-existing DataTable i.e without writing to another excel file?

@bp777 - I am already writing in the same excel file only. Please see the screenshot again.

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