How to remove trailing space in an excel column

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
as you can see above there is a spacing behind the value.

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

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

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

Use regex replace activity.

Hi @AkshaySandhu,

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

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

@harizd,

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

2 Likes

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

@harizd,

Could you please send me your workflow.

Hi @lakshman

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

@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

Hello @lakshman

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

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.

I see. thank you for the explanation @lakshman!

1 Like

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