Creating a macro to add decimal to left justified value

rjmcconne · PostJan 28, 2017#12017-01-28T18:31+00:00

I have a file that contains a left justified numeric value (space filled to the right) in which I need to place a decimal in front of the last two digits. Normally, not an issue for me if the value count is consistent. However, the value count won't always be consistent.

This is an example of what the file looks like:

Code: Select all

ABC123456789XYZ20045    XYZ
ABC123456789XYZ100065   XYZ
ABC123456789XYZ4563326  XYZ

This is how I want it to look.

Code: Select all

ABC123456789XYZ200.45   XYZ
ABC123456789XYZ1000.65  XYZ
ABC123456789XYZ45633.26 XYZ

The starting position will always start in the same position (16) every time and the next field will always start in the same position (25).

I'm looking for a way to add the decimals without having manually do it since this file could have a few hundred rows at a time.

Any help is appreciated.

Ovg · PostJan 28, 2017#22017-01-28T20:22+00:00

Try Search->Replace

Find what: (\d\d +\D+)$
Replace with: .\1
Check Regular expressions: and choose Perl
Check Replace all from top of file
Press replace all

rjmcconne · PostJan 30, 2017#32017-01-30T14:23+00:00

Thank you. That really helped. One last question, if that value that we put the decimal is extended out to a max of 10 bytes, what would need to used in that instance? The example I showed went up to a max of 7 bytes, but for my file, it could also have 8, 9, or 10. Your solution definitely works for the seven bytes I put in the examples. I've been playing around with it to see if I could get it to account for those three other scenarios but couldn't quite figure it out.

Ovg · PostJan 30, 2017#42017-01-30T15:31+00:00

Sorry, I don't understand what do you want. Please give some full example ....

rjmcconne · PostJan 30, 2017#52017-01-30T16:13+00:00

Sorry about that.

Here's a complete example with all scenarios.

Current File:

Code: Select all

XX1234567891000      0127201717721    
XY1234567891001      01272017177      
XX1234567891002      012720177805     
XY1234567891003      01272017780512   
XX1234567891004      012720177805123  
XX1234567891005      0127201778051234 
XY1234567891006      01272017780512345

This is how I want it to look:

Code: Select all

XX1234567891000      01272017177.21    
XY1234567891001      012720171.77      
XX1234567891002      0127201778.05     
XY1234567891003      012720177805.12   
XX1234567891004      0127201778051.23  
XX1234567891005      01272017780512.34 
XY1234567891006      012720177805123.45

The previous item you provided worked perfectly on my first example. I've updated the file a little bit from what it was before. I essentially want to be able to find the last two digits in that string and place decimal in front of the last two digits. The find and replace you provided works on the first 5 rows, but not on the file two.

Ovg · PostJan 30, 2017#62017-01-30T16:36+00:00

Well. Please try:
Find what: (\d\d *\D*)$
Replace with: .\1
Regular expressions: Perl

Should work for both examples.

rjmcconne · PostJan 30, 2017#72017-01-30T18:03+00:00

That worked like a charm. Thank you so much.