Want to remove space in every cell in the Excel

Hi All,
Am trying to remove space in every value but i have a problem as there is no headers, empty cells available and few cell contain no space, so how can I over come with this process.
Query.xlsx (8.1 KB)
the Excel file for ur referance.


Follow the steps…

Read the data into datatable…

  1. Use for loop onthe datatable(currentrow)
  2. Inside the loop …use another loop(item) for columns on Enumerable.Range(0,dt.Columns.count).ToArray and change type argument to int32
  3. Inside the second loop use assign
    Currentrow(item) = currentrow(item).ToString.Trim
  4. Writw the data back to excel


Hi @ragul.n ,

Could you give this workflow a try?


//Clears whitespaces from both ends

(From row In dt.AsEnumerable()
Let rlst = row.ItemArray.ToList()
Let ra = rlst.ConvertAll(Function(c) c.ToString.Trim).ToArray()
Select dt_res.Rows.Add(ra)).CopyToDataTable()

RemoveSpacesAllCells.xaml (8.4 KB)

Kind Regards,
Ashwin A.K

Hi Users ,

use Replace regular expression…

Happy Automation …!!!

Hi All found the Solution and the steps are following,
1.Reading the Excel
2.Getting the total count of the column
3.Inside For Each using if (initial count <= Total count)
4.Within that If using another For Each to trim the Every column using Assign along with initial counter.

But thanks for your suggestions.

Happy Automation.

Thanks Ashwin

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