Regex format

hello, please your help.

I have a process where it must be validated that all the cells of a column comply with a specific format, which is the following.

ddd x ddd x ddd x ddd

ex.(70 x 40 x 120 x 12)

the format can have a minimum of 1 digit and a maximum of 3 digits.

if the format doesn’t match, let the bot correct it…

An example of an incorrect form would be

34cm x 14cm x 120 x 15

Can someone guide me to do this type of validation with regex?

grafik
\b(\d{1,3} x ){3}\d{1,3}\b

Great, but how can I do the validation and correct the ones that are wrong?

EX: row 3 and 4 are wrong how can I correct it so that it does mach with the regex

image

r

Hi,

We can correct the above data using the following Regex.Replace method, for example

CurrentRow("Data") = System.Text.RegularExpressions.Regex.Replace(CurrentRow("Data").ToString,"\D*(\d+)[^\dx]*x[^\dx]*(\d+)[^\dx]*x[^\dx]*(\d+).*","$1 x $2 x $3")

Please note that the above cannot recover all the wrong pattern.

Sample20220510-2.zip (8.7 KB)

Regards,

1 Like

Might you help me?

I have to validate that the data in the 6 columns complies with the regex format, if it does not comply, remove the characters that do not correspond, or add those that do correspond (in the event that the spaces are not respected)

If there are empty data in columns 2,3,4,5,6, it is also correct.

thanks

Hi,

How about the following sample? Does this work for you?

Sample20220510-2v2.zip (8.9 KB)

Regards,

1 Like

it didn’t work

Hi,

Did you try Sample20220510-2v2.zip in my previous post? The expression is as the following.

System.Text.RegularExpressions.Regex.Replace(CurrentRow(column.ColumnName).ToString,"\D*(\d+)[^\dx]*x[^\dx]*(\d+)[^\dx]*x[^\dx]*(\d+).*","$1 x $2 x $3")

Regards,

I am trying only with one column but it doesn’t work.

Hi,

Can you share your input data and expected output data as file? It’s no problem if dummy data.

Regards,

input data:
image

output data:

image

Hi,

Sorry, the above my expression supports only 3 numbers. The following will work for 4 numbers. Can you try this?

 System.Text.RegularExpressions.Regex.Replace(CurrentRow(column.ColumnName).ToString,"\D*(\d+)[^\dx]*x[^\dx]*(\d+)[^\dx]*x[^\dx]*(\d+)[^\dx]*x[^\dx]*(\d+).*","$1 x $2 x $3 x $4")

Regards,

1 Like

Hi,

This error shows your datatable doesn’t have column named “Data”. Please modify “Data” to your actual column name.

Regards,

how do i do it?


this is my Sequence…

Hi,

Can you share screenshot of the worksheet?

If it’s same as the above input image, the following will work. (If addheader option is off)

Row(0) = System.Text.RegularExpressions.Regex.Replace(Row(0).ToString,"\D*(\d+)[^\dx]*x[^\dx]*(\d+)[^\dx]*x[^\dx]*(\d+)[^\dx]*x[^\dx]*(\d+).*","$1 x $2 x $3 x $4")

Regards,

Its a example. just for test

Hi,

For now, can you try as the following? (Please turn off at add headers option in ReadRange)

Row(0) = System.Text.RegularExpressions.Regex.Replace(Row(0).ToString,"\D*(\d+)[^\dx]*x[^\dx]*(\d+)[^\dx]*x[^\dx]*(\d+)[^\dx]*x[^\dx]*(\d+).*","$1 x $2 x $3 x $4")

The following sample will also help you.

Sample20220510-2v3.zip (8.9 KB)

Regards,

Great, if it worked.

but now the challenge is to apply it to these 6 columns.

How could I do the instruction of that?