How to fetch just number from a table column which may have date value also present

Help Activities
1

Hello Everyone,

There’s a table in which there’s a ‘Remarks’ column.
There can be multiple rows in this ‘Remarks’ column.

My requirement is i am supposed to loop through the rows and find if any number is present in the remarks. For example:10
The moment i find the number i will break the loop.

Sometimes this ‘Remarks’ coulumn can have date value.For example 12/12/2022. The date can be present in any format.
If the remarks is having only date value then i should not consider that row.

But sometimes the ‘Remarks’ coulumn can have the date value along with number. This number can be present before or even after date value.
In this case I am supposed to fetch the number.

Currently i am facing challenge in getting this number when date value is also there.
It’s fetching incorrect number.

Can anyone help please.

2

@Deepa_Madkar

Please try using this regex

(^\d+\s|\s\d+$|^\d+$)

image

cheers

3

Thank you @Anil_G for the quick reply.
The date format can be any.
Just wanted to know in case if date format is 20-Oct-2022 instead 20/10/2022 then how to generalise the regex pattern which you have suggested.

Thank you in advance

4

Hi @Deepa_Madkar

I am not using the date format at all…

So let me explain the regex

^\d+\s - Matches number at start with a space between next characters
\s\d+$ - Matches number at end with a space between previous characters
^\d+$ - Only a number from start to end of string

image

cheers

5

Thank you for explaining.
Just one last question,
so in the process suppose say this condition met then i m supposed to capture this value and move ahead
the regex which you have provided now if i use in ‘If condition’ then it shows string cannot be converted to boolean.
How can i convert this string to boolean.

As i mentioned in my query if only the value is present irrespective of date then i should break the loop else i need to continue the loop.

It will be really helpful if you can guide.

Thanks

6

Hi @Deepa_Madkar

Ideally in condition you have to check IsNothing(regexvaluereturned) .so that when a value is returned this will be false else it will be true

Cheers

7

Thanks for the reply @Anil_G
I tried to use your solution.
It works fine when the value is after date.
But if the value is before date then its giving incorrect result.

For example if Remark is :
“Line limit is 10 on 12 oct 2020”
then value getting extracted is 2020 instead of 10

Similarly if remarks is as follows:
“Line limit is 10 on 12/12/2020”
then value is not getting extracted

Thank you

8

@Deepa_Madkar

Can you provide few samples…because the regex was written assuming you will have only date and number no other characters

Is the given structure followed always? or any other formats are also there?

cheers

9

Hi @Anil_G Its a Remarks column in a table where some remarks will be there. That remark may just contain date/value/date and value
Need to extract that value if there.

For example some sample values shown as below:

Remarks
Line limit is 10 on 3/10/2022
New line on 5 oct 2022 is 2
Line is 20 as on 3/15/2020
approved lines on 2 jan 2020 is 30
line value is 25
Date is 23/3/2022

Only value i should extract. If some remarks row is having only date then i should skip that row

Thank you

10

@Deepa_Madkar

Can you please provide variations without date as well please

But looking at the pattern not sure one will fit…will check on it

cheers

11

@Deepa_Madkar

Please try this

(?<!\d*(/|-|[a-zA-Z]{3} *)\d*)\d+(?!\d*(/|-| *[a-zA-Z]{3})\d*)

image
image828×862 76 KB

Cheers

1 Like
12

Hi @Anil_G thank you for taking out your time to help me.
I tried your regex

But while running it gave me error saying “Nested quantifier *”

If i removed * from regex wherever it is then result is incorrect.

13

@Deepa_Madkar

Did you give something wrong …because I can see it

image

cheers

14

Thanks @Anil_G . It’s working. I just added space character before * which initially i had not.
If possible can you please explain this piece of regex.
Regex i have not worked as such before. Does space character also has some importance?

Thank you once again

15

@Deepa_Madkar

Okay thats the reason i added apace to check for 0 or more spaces …if you have removed then the expression is |* which is invalid…pipe is like a or condition and * is for one or more …

| * means previous expression or space 0 or more times

Hope this is clear

Cheers

16

Yes…thank you for helping out @Anil_G

1 Like
17

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