Details of a Substitution Regular Expression Example

The regular expression described here is used in Substituting a Regular Expression for a Recordset Field. This example changes a value usually displayed with a trailing minus sign to a value with a leading minus sign.

Search for: (^|[^-.\d])(\d+(?:\.\d+)?)-(?=[^-.\d]|$)

Replace with : $1-$2

A "capture group" is a regular expression surrounded by parentheses that is remembered as a numbered variable for use in the replacement.

Detail of Expression Description
(^|[^-.\d])
A numbered capture group
(Becomes $1 in the replacement)
Select from 2 alternatives:
Beginning of line or string
Any character that is not in this class: [-.\d] (minus, decimal, or digit)
(\d+(?:\.\d+)?)
A numbered capture group
(Becomes $2 in the replacement)
\d+(?:\.\d+)?
Any digit, one or more repetitions
Optional (zero or one repetitions) of dot followed by one or more digits
- (Match on trailing minus)
([^-.\d]|$)
Match a suffix but exclude it from the capture.
Select from 2 alternatives:
Any character that is not in this class: [-.\d] (minus, decimal, or digit)
End of line or string

This translates a string like "123.45-" to "-123.45". Any leading non-numeric characters are retained and trailing non-numeric characters are removed so that "ABC123.45-XYZ" would be changed to "ABC-123.45".

 

Related Topics
Bullet How to use Regular Expressions