Hi there,
I'm trying to use the RegEx Replace formula in the Dynamic Rename tool to remove the underscores from the name. However, I only want the underscores removed from around the words in the name and then replace the underscores between the numbers to a slash for a date format.
An example of my field names are:
_ABC_Corp_123456_5_31_2023
_ABCBank_123456_5_31_2023
I know I've set up my formula incorrectly because the results are either coming back with Field_6 and Field_7 or blank and blank 2. These were the formulas I was trying:
This one to take the first underscore and replace it with nothing - gives me back Field_6.
This one to take the underscores between the letters to replace with a space, doesn't work since the prior formula gives back Field_6.
Could someone help me with this?
Thank you!
Solved! Go to Solution.
Do you want a space after the ABC_Corp and ABCBank? Or should the date come directly after that?
The below will yield "ABC_Corp5-31-2023"
REGEX_Replace([_CurrentField_], "^(\_)(\D+)(\_\d+)(\_)(\d+)(\_)(\d+)(\_)(\d+)", "$2$5-$7-$9")
Capture Group 1 - Underscore (\_)
Capture Group 2 - Any non Digit (\D+)
Capture Group 3 - Underscore followed by Digits (\_\d+)
Capture Group 4 - Underscore (\_)
Capture Group 5 - Any Digits (\d+)
Capture Group 6 - Underscore (\_)
Capture Group 7 - Any Digits (\d+)
Capture Group 8 - Underscore (\_)
Capture Group 9 - Any Digits (\d+)
This will yield "ABC_Corp 5-31-2023"
REGEX_Replace([_CurrentField_], "^(\_)(\D+)(\_\d+)(\_)(\d+)(\_)(\d+)(\_)(\d+)", "$2 $5-$7-$9")
Binuacs works better. I forgot about the 123456 😬
@binuacs thank you so much, this worked perfectly!
Do you consider to use
Replace([_CurrentField_], '_', '')
which it replaces your '_'?