Extract two string with number

[fikrizahari]

Hi,

I am trying to use regex to extract two words that contain number.

Description

Wages up to HKD30

When wages exceed HKD30 but not HKD50

When wages exceed HKD50 but not HKD70

When wages exceed HKD70 but not HKD100

Wages exceed HKD100

This is to create number range as below :

Description	Start	End
Wages up to HKD30	0	30
When wages exceed HKD30 but not HKD50	30	50
When wages exceed HKD50 but not HKD70	50	70
When wages exceed HKD70 but not HKD100	70	100
Wages exceed HKD100	100	999

my existing regex is : 
regex_Replace([Description],'.*(HKD[\d,\.]+).*', '$1')

However, its only find the last word that contain number.
Kindly advise if possible, to extract 2 ( two ) words that contain number.

[Raj]

one way to do this

[Yoshiro_Fujimori]

If you are sure that there are maximum two numbers, you may want to use "Tokenize" option of RegEx tool.

[fikrizahari]

Hi Raj and Yoshiro,

its really great to see the solution working so well.
I got another question, but its just upgrade from previous question.

What if the string mentioned something like :

When wages exceed HKD9,000.00 but not HKD10,000.00

So I would like the range become : 9,000.00 to 10,000.00
Thanks !

[Yoshiro_Fujimori · Accepted solution]

@fikrizahari ,

So you want to add ","(comma) and "."(period) in addition to numbers.

In that case, you may change the RegEx as below:

[\d\.,]+

"\." means period (need to escape with "\".)

Output:

If you want to be more specific (e.g. if you want to exclude "9,00.00"), you would need to tweak the RegEx. Good luck.

[binuacs]

@fikrizahari One way of doing this

[fikrizahari]

Hi @Yoshiro_Fujimori  and @binuacs,

Both solutions working so well :)
Thank you for the help !