Regex to replace any value having special character in string like 2121k$ , 2121k€, 1000k CHF, 1000k EUR, 1000k USD

bihitakdass 

Let me know if that works for you.

regexreplaceall("\d+k\s*(?:[$€]|CHF|EUR|USD)", PUT YOUR STRING HERE ," ")

Not sure about your exact requirements. You can try the following if the prefix and postfix string are fixed:

left("Test 1000k USD test data set", 5) & right("Test 1000k USD test data set", 13)

There may be a more elegant way but it's easiest to understand and implement as two expressions:

1. an expression that takes as its input the data you want processed and performs a fn!reduce() on it, using the list of currencies you want to find and replace with the space character
2. a helper expression rule which is called by the above rule, which is called recursively by the fn!reduce() for each currency you want replaced. This uses the fn!regexreplaceall which uses a pattern to match on the string being and replaces all matches with, in this example, a space

The first rule looks something like this:

fn!reduce(
  rule!SJB_ER_replaceCurrencyValue,
  ri!data,
  { "\$", "€", "CHF", "EUR", "USD" }
  /* note that the dollar symbol in the list above includes the backslash escape character as */
  /* the dollar symbol has a speical menaing in regex expressions */
)

and the second rule looks like this:

fn!regexreplaceall(
  concat("[0-9]*k", ri!currency),
  ri!data,
  " "
)

If you need any further explanation as to what's happening here please ask.

Hi Shubham,
Thanks this worked to certain extent but can you also suggest something for 

1. Test 0.5M EUR Test :--> Test Test

2. Test 2121K€ test data :--> Test test data

3. Test 110EUR test data :--> Test test data

4. Test 7.95USD test data :--> Test test data 

This k can be in caps also like 211K and not just K, it can be Billion("b" ,"B") ,Million( "m" , "M") 

So can you help in modifying the regex to accept 

The pipe character in RegEx acts as an "OR" operator - so you can extend the RegEx pattern accordingly:

regexreplaceall("\d+[k|K|m|M|b|B]\s*(?:[$€]|CHF|EUR|USD)", ri!data ," ")

Shubham Aware "(?:[$€]|CHF|EUR|USD)" can you please share the purpose of ?: in this regex?

bihitakdass  Let me know if that works for you

regexreplaceall(
  "\b\d+(\.\d+)?[kKmMbB]?\s*(?:[$€]|CHF|EUR|USD)\b",
  PUT YOUR STRING HERE,
  " "
)

 Yogi Patel 
 (?:) is used for non-capturing groups.
?: says "find any of these on the list," but you don't need to remember which one you actually found later.