How to trim in a datatable column?


#1

hi,

I’m trying to trim a particular information in one of the columns in my data table. highlighted in the column is the only information I need to get.

image


#2

Hi.

I would recommend you use a Regex pattern, which there are various patterns you can use.

Let’s assume you have a For each row, then an Assign to change the value of each row.
It would look like this:

rPattern = "(.*)(?=(\-[A-Za-z]{2,3}\-[A-Za-z]{2,3}))" //declare in Variables

For each row In dt1
    Assign row("Case Number & Name:") = System.Text.RegularExpressions.Regex.Match(row("Case Number & Name:").ToString.Trim, rPattern).Value

And, there ya go. Hope that helps.

EDIT: the pattern says to take anything (.*) that is before (?=( dash Alphas(2-3 characters) dash Alphas(2-3 characters)

Regards.


#3

thanks ClaytonM. i will try it and let you know if I run into any issues.


#4

Hey @JayR,
@ClaytonM had already given you the best way. But, if you are not clear with Regex then split that row value with a delimiter (here it is “-”). With this you get an array (of string) as output and next you can concatenate the string items as you need. This is a basic and lengthy way :smiley: . You can try this as an alternative to REGEX.

Best Regards,
SP


#5

do you have a sample workflow that applies regex? I appreciate the help


#6

Please share the excel file here…


#7

Use The Regular expresion

System.Text.RegularExpressions.Regex.Match(YOURSTRINGVARIABLE,"\d+:\d+-\w+-\d+").ToString.Trim


#8

here’s my sample data. i also included the workflow where I added the instructions from ClaytonMExtract Data from Site.xaml (10.2 KB)
Sampledata.xlsx (11.6 KB)


#9

@JayR

Above statement is working fine. Use that statement in for loop.


#10

@JayR ,
please check the attached workflow.

FYI… I have added both ways .i.e. Regex and string manipulation.
Extract Data from Site.xaml (12.7 KB)

Regards,
SP


#11

@SantoshPothina thanks a lot. i tried and regex worked.


#12

thanks everyone for your help