Regex format

[alexis.mendoza]

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?

[ppr (Peter Preuss)]

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

[CheatSheet] - System.Text.RegularExpressions | RegEx

This CheatSheet introduces the basic use of regex functions. With further examples also special cases are presented. Introduction From the namespace System.Text.RegularExpressions following methods are offered: Regex.Match Regex.Matches Regex.isMatch Regex.Replace Regex.Split A simple usage for example would look like this: [grafik] Recommendation: add System.Text.RegularExpressions to the imports: [grafik] it allows to use the shortened statement, as the namespace part can be ommited…

[alexis.mendoza]

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

r

[Yoichi]

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,

[alexis.mendoza]

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

[Yoichi]

Hi,

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

Sample20220510-2v2.zip (8.9 KB)

Regards,

[alexis.mendoza]

@Yoichi

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

it didn’t work

[Yoichi]

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,

[alexis.mendoza]

@Yoichi

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

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

[Yoichi]

Hi,

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

Regards,

[alexis.mendoza]

@Yoichi

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

input data:

output data:

[Yoichi]

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,

[alexis.mendoza]

@Yoichi

\D*(\d+)[^\dx]*x[^\dx]*(\d+)[^\dx]*x[^\dx]*(\d+)[^\dx]*x[^\dx]*(\d+).*","$1 x $2 x $3 x $4")

[Yoichi]

Hi,

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

Regards,

[alexis.mendoza]

@Yoichi

\D*(\d+)[^\dx]x[^\dx](\d+)[^\dx]x[^\dx](\d+)[^\dx]x[^\dx](\d+).*“,”$1 x $2 x $3 x $4")

how do i do it?

[alexis.mendoza]

this is my Sequence…

[Yoichi]

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,

[alexis.mendoza]

Its a example. just for test

[Yoichi]

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,

[alexis.mendoza]

Great, if it worked.

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

How could I do the instruction of that?