Want to remove space in every cell in the Excel

[ragul.n]

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.

[Anil_G (Anil Gorthi)]

@ragul.n

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

Cheers

[ashwin.ashok ((TheCodingTheory))]

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

[anon87560229]

Hi Users ,

use Replace regular expression…

Happy Automation …!!!

[ragul.n]

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.

[ragul.n]

Thanks Ashwin