How to remove trailing space in an excel column

Help
, ,
1

Hello everyone,

I have a data table consist of 4k rows of data and I need to trim a trailing space in a particular column called JO No.

image
image421×531 9.55 KB

image
as you can see above there is a spacing behind the value.

image
image436×891 13.4 KB
image

This is what I have come up with to the trim the spaces in the column but it does not seem work properly. Could anybody point out what I should do to fix this workflow?

YPD Variance map testing.zip (149.6 KB) here is my workflow

Thank you,
HarizD

2

@harizd
have you tried trim function
row(“JO No”) = row(“JO No”).ToString.Trim

3

Just curious why you want to do it in the UiPath, can’t you do it in the excel Replace all and apply the rule what you applied in the automation ?

Alternatively, you can use str = str.Replace(" ", [String].Empty) or Regex.Replace(“your_str”, “\s+”, “”)or trim

3 Likes
4

Use regex replace activity.

5

Hi @AkshaySandhu,

Yes, I have tried it recently. Unfortunately there are still spaces inside the cells

6

doing it with excel VBA? I’m not good with VBA which is why i resort to using UIpath :sweat_smile: I’ve tried str = str.Replace(" ", [String].Empty) and trim, but there were no changes made to the column. As for regex i got an error:
image

7

@harizd,

Try below one: System.Text.RegularExpressions.Regex.Replace(row(“JO No”), “\s+”, “”).toString

2 Likes
8

Hi @lakshman
thank you for the suggestion. This error came out when I tried that though
image

9

@harizd,

Could you please send me your workflow.

10

Hi @lakshman

Here is my workflow
YPD Variance map testing.zip (149.6 KB)

11

@harizd,

I modified your workflow according to your requirement and please find the attached file. If you have any doubts then please let me know.

For testing purpose, I put some spaces in ‘JO No’ column values and test it and let me know.

YPD Variance map testing.zip (150.2 KB)

1 Like
12

Hello @lakshman

Thank you so much for this it worked well. :smiley: I have a question. Why is this +2?
image

13

Because, index will start from ‘0’ here and also we don’t want to write anything in first row because it is header. So, we will add +2 then it will start writing from second row onwards.

14

I see. thank you for the explanation @lakshman!

1 Like
15

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