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!
Solved! Go to Solution.
Couldn't you use a text to columns tool and use \s as your delimiter to split on the space?
Text to column on the decimal breaks all of the other rates over 2 rows .24 1 and then the .24 etc instead of 1.24
The issue is the change in structure from the short term (top) rates to the mid-term rates.
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!
Right, I was specifying a space, not a decimal.
That worked perfect. Thanks
@BrandonB - the issue is that the rows are now mis-aligned. AFR (nothing here...but 100) is one column to the left of the others.
Gotcha! @lmorrell has the solution to go with in that case!