Remove leading spaces without using loops

Help Activities
, , ,
1

Hi, I want to Trim an excel column tp remove all the leading or tailing spaces; for example

Capture1

In the above table i have both number and Text data so i want to trim the extra spaces and arrange them equally i know how we do it in excel but i have no idea to how to automate this because i contains 3000+ rows so i cannot use loop. I want to get the column just like the “B Cell” (in screenshot) Any idea on this.

Thanks

2

@MLT
in general we can do:

  • readin Excel into datatable
  • process data in datatable
  • write out to excel

in case of that a trim in all rows and in all columns will be ok for you, then have a look here:
Trim_AllCellsAllRows.xaml (7.8 KB)

2 Likes
3

Hi @ppr,
Thanks you so much, your solution helped me a lot. I have a doubt here…
Is there is a way we can do the same action using single datatable?

Thanks!

1 Like
4

Hi,
I made below change in the code and it worked fine using single datatable

(From r In dtData.AsEnumerable
Let ra = r.ItemArray.Select(Function (e) If(isNothing(e), Nothing, e.toString.trim)).toArray()
Let dtData=dtData.Clone
Select dtData.Rows.Add(ra)).CopyToDataTable()

Thanks!