Tapatalk

How to insert a decimal point before the last two digits on all values using a Perl regular expression?

How to insert a decimal point before the last two digits on all values using a Perl regular expression?

14
Basic UserBasic User
14

    Dec 09, 2020#1

    Hello!

    Code: Select all

    10767 00 00 10767
    01 00 00 01
    9186 00 00 9186
    01 00 00 01
    63990 00 978 63012
    06 00 00 06
    26145 00 00 26145
    03 00 00 03
    82973 11593 00 71380
    07 01 00 06
    228479 11593 87462 129424
    20 01 08 11
    33027 00 00 33027
    03 00 00 03
    232079 11593 83401 137085
    20 01 07 12
    Suppose this is the file in Excel. Please tell me coding of Perl of 2 decimal places in all columns.

    6,685587
    Grand MasterGrand Master
    6,685587

      Dec 09, 2020#2

      UltraEdit is a text editor. It cannot process binary data stored in a *.xls or *.xlsx file. It can process data stored in a comma-separated values file with using a comma , or a semicolon ; or a vertical bar (pipe) | or a horizontal tab character as separator and having usually the file extensions .csv or .tsv. Such files are not Excel files. Excel is just one of many applications which can read in files with comma-separated values (not really 100% correct regarding to multi-line values).

      I do not understand what you really want. Please post an example of the file on being opened in UltraEdit and what you want as result of running one or more Perl regular expression replaces. Three to five lines should be enough to explain how the data should be processed by the regular expression replace(s).

      Please use the last but one item (second from right side) on the toolbar above the edit field in your browser to format the selected input and the selected output example lines in your next post as code block as I have done on your initial post.
      Best regards from an UC/UE/UES for Windows user from Austria

      14
      Basic UserBasic User
      14

        Dec 13, 2020#3

        Suppose this is the data in Excel

        Code: Select all

        15597 000 000
        030 000 -030 000 -030
        991
        002
        8146 000 000
        015 000 054 000 054
        149743 000 000
        374 000 -374 000 -374
        I want to insert a decimal point in every column. The output should be:

        Code: Select all

        155.97 0.00 0.00
        0.30 0.00 -0.30 0.00 -0.30
        9.91
        0.02
        81.46 0.00 0.00
        0.15 0.00 0.54 0.00 0.54
        1497.43 0.00 0.00
        3.74 0.00 -3.74 0.00 -3.74
        Please help me with an appropriate regular expression find/replace in UltraEdit.

        11327
        MasterMaster
        11327

          Dec 13, 2020#4

          1. Make copy of your data
          2. Find what:

          Code: Select all

          \d+\K(\d\d)
          3. Replace with:

          Code: Select all

          .\1
          4. Mark Regular expressions: and choose Perl
          5. Mark Replace all is from top of file (if needed)
          6. Click Replace all
          It's impossible to lead us astray for we don't care even to choose the way.

          6,685587
          Grand MasterGrand Master
          6,685587

            Dec 13, 2020#5

            The Perl regular expression find/replace as posted by Ovg works fine using a backreference.

            Another possible Perl regular expression replace is using as search string (?=\d\d\>) and a single dot . as replace string. The search expression uses a positive look-ahead to check on each character in the file if there are next two digits at end of a word without selecting them. If this condition is true, the replace is done by inserting the decimal point.
            Best regards from an UC/UE/UES for Windows user from Austria

            14
            Basic UserBasic User
            14

              Dec 13, 2020#6

              Thank you very much Ovg, and also grateful thanks to Mofi.

                May 26, 2021#7

                Hi!

                I have the data:

                Code: Select all

                30434 -38 38
                28532 -35 35
                1446 -02 02
                -1446 02 -02
                -28532 35 -35
                -30434 38 -38
                426770 951 10 407 534
                -426770 -951 -10 -407 -534
                I want to insert a decimal point in every column. The output should be:

                Code: Select all

                304.34 -.38 .38
                285.32 -.35 .35
                14.46 -.02 .02
                -14.46 .02 -.02
                -285.32 .35 -.35
                -304.34 .38 -.38
                4267.70 9.51 .10 4.07 5.34
                -4267.70 -9.51 -.10 -4.07 -5.34

                6,685587
                Grand MasterGrand Master
                6,685587

                  May 26, 2021#8

                  Run first a Perl regular expression with search string \<(\d\d)\> and replace string 0.\1 and run next a second Perl regular expression replace with \d\K(\d\d)\> as search string and .\1 as replace string.

                  The result for the tab separated input example data is:

                  Code: Select all

                  304.34 -0.38 0.38
                  285.32 -0.35 0.35
                  14.46 -0.02 0.02
                  -14.46 0.02 -0.02
                  -285.32 0.35 -0.35
                  -304.34 0.38 -0.38
                  4267.70 9.51 0.10 4.07 5.34
                  -4267.70 -9.51 -0.10 -4.07 -5.34
                  The search string  \<(\d\d)\> means:

                  \< ... find a string at beginning of a word. Space, horizontal tab, line-feed, hyphen, dot are not word characters. So a word is for the Perl regular expression just a series of digits for the input data.

                  (...) ... mark the string found by the expression inside the first pair of round brackets for being back-referenced in search or replace string with \1 as done in the replace string to keep the two digits.

                  \d\d ... find two digits.

                  \> ... the two digits must be at end of a word.

                  In other words this search expression matches only numbers with exactly two digits. The minus sign (in real the hyphen character) is not a word character.
                  he replace string inserts the digit 0 and a dot left to the two found digits which are kept due to back-referencing them.

                  The second search expression \d\K(\d\d)\> means:

                  \d ... find a digit.

                  \K ... keep back the matched digit which means unselect the already matched digit and start selecting found string from this position in character stream.

                  (...) ... mark the string found by the expression inside the first pair of round brackets for being back-referenced in search or replace string with \1 as done in the replace string to keep the two digits.

                  \d\d ... find two digits.

                  \> ... the two digits must be at end of a word for a positive match.

                  In other words the search expression searches for three digits at end of a word, ignores the first digit for the replace and inserts a dot left the two found digits which are kept due to back-referencing them.
                  Best regards from an UC/UE/UES for Windows user from Austria

                  14
                  Basic UserBasic User
                  14

                    May 26, 2021#9

                    Thanks a lot. I can also use:

                    Find what: (?=\d\d\>)
                    Replace with: .

                      Jun 04, 2021#10

                      I have following data:

                      Code: Select all

                      2,480.24 1,455.00
                      1,664 1,666 1,562.00
                      208.14
                      -1,134
                      1,223
                      -138.54
                      148.91
                      -134
                      2,835
                      -9.72
                      346.12
                      -2,517
                      2,275
                      -310.64
                      283.20
                      -2,060
                      2,060
                      -245.06
                      245.06
                      -1,373
                      1,373
                      -165.70
                      165.70
                      1,750.57
                      
                      I want to highlight only the data with comma and decimal dot which means:

                      Code: Select all

                      2,480.24 1,455.00
                      1,562.00
                      1,750.57
                      
                      Which Perl regular expression could be used for that purpose?

                      6,685587
                      Grand MasterGrand Master
                      6,685587

                        Jun 04, 2021#11

                        There can be used the Perl regular expression: \<(?:\d+,)+\d+\.\d+\>

                        \< ... find a string at beginning of a word.

                        (?:...) ... defines a non-marking group.

                        \d+, ... find a digit one or more times followed by a comma.

                        + ... the non-marking group must be applied one or more times.

                        \d+ ... find a digit one or more times

                        \.\d+ ... find next a dot which must be escaped with a backslash to be interpreted as literal character and one or more digits.

                        \> ... the last digit must be at end of a word.

                        The expression matches also a value like 1,234,567.89.
                        Best regards from an UC/UE/UES for Windows user from Austria

                        14
                        Basic UserBasic User
                        14

                          Jun 04, 2021#12

                          Thank you.

                          What should I use to replace these commas so that 1,234,567.89 becomes 1234567.89?

                          19176
                          MasterMaster
                          19176

                            Jun 04, 2021#13

                            Hi,

                            Are there commas in other context, too?
                            If no, then simply replace all commas with nothing. Otherwise the context is needed.
                            My blind Perl regexp guess:
                            F: \d\K,(?=\d)
                            R: nothing

                            BTW a bug found in UE Perl regexp engine - lookbehind often not does not work as supposed, already reported to IDM but still not fixed. Hence \K used.

                            BR, Fleggy

                            14
                            Basic UserBasic User
                            14

                              Jun 04, 2021#14

                              Yes there are commas in other context too, but I only want to remove the commas where there is a decimal dot.

                              19176
                              MasterMaster
                              19176

                                Jun 04, 2021#15

                                Well, then for example 1,123 shoud stay intact because there is no decimal point?
                                Perl regexp for numbers containing decimal point:
                                F: \d\K,(?=[\d,]+?\.)

                                Perl regexp for numbers with or without decimal point:
                                F: \d\K,(?=\d)

                                Read more posts (3 remaining)