Tapatalk

Creating a macro to add decimal to left justified value

Creating a macro to add decimal to left justified value

4
NewbieNewbie
4

    Jan 28, 2017#1

    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.
    11327
    MasterMaster
    11327

      Jan 28, 2017#2

      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
      It's impossible to lead us astray for we don't care even to choose the way.
      4
      NewbieNewbie
      4

        Jan 30, 2017#3

        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.
        11327
        MasterMaster
        11327

          Jan 30, 2017#4

          Sorry, I don't understand what do you want. Please give some full example ....
          It's impossible to lead us astray for we don't care even to choose the way.
          4
          NewbieNewbie
          4

            Jan 30, 2017#5

            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.
            11327
            MasterMaster
            11327

              Jan 30, 2017#6

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

              Should work for both examples.
              It's impossible to lead us astray for we don't care even to choose the way.
              4
              NewbieNewbie
              4

                Jan 30, 2017#7

                That worked like a charm. Thank you so much.