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!

[BrandonB]

Couldn't you use a text to columns tool and use \s as your delimiter to split on the space?

[Gio]

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.

[lmorrell · Accepted solution]

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!

[BrandonB]

Right, I was specifying a space, not a decimal. 

 

[Gio]

That worked perfect.  Thanks

[Gio]

@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.

[BrandonB]

Gotcha! @lmorrell has the solution to go with in that case!