Adding a 0 before a decimal in a string

Gio

I have a series of rates that come to me as string values.  I am able to use the RegEx function to get the bottom (mid-term) rates parsed into columns (which I'll then convert to values), but the parse won't pick up the first group because there is no digit prior to the decimal.  I assume there are two options, find a way to add a 0 before the RegEx, or figure out how to get the RegEx to work off of the decimal.

Here's the RegEx as written

(.*)\s(..\d\d[%])\s(\d\.\d\d[%])\s(\d\.\d\d[%])\s(\d\.\d\d[%])

Data to be parsed:

AFR .20% .20% .20% .20%
110% AFR .22% .22% .22% .22%
120% AFR .24% .24% .24% .24%
130% AFR .26% .26% .26% .26%

Mid-term
AFR 1.27% 1.27% 1.27% 1.27%
110% AFR 1.40% 1.40% 1.40% 1.40%
120% AFR 1.53% 1.52% 1.52% 1.52%
130% AFR 1.66% 1.65% 1.65% 1.64%
150% AFR 1.92% 1.91% 1.91% 1.90%
175% AFR 2.23% 2.22% 2.21% 2.21%

Thanks for the help in advance!

lmorrell

Hi @Gio, 

Solution is attached.

If your aim is to maintain all the records on the same line in a single step, then the below statement should work for your sample data.

(.*)\s(\d*\.\d\d[%])\s(\d*\.\d\d[%])\s(\d*\.\d\d[%])\s(\d*\.\d\d[%])

The change from your original statement is to add the option for "Zero or more digits" prior to the decimal point (\d*\.) within each capture group. 

Hope this helps!

Adding a 0 before a decimal in a string.yxmd