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

PostDec 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,824625
Grand MasterGrand Master
6,824625

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

14
Basic UserBasic User
14

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

11427
MasterMaster
11427

PostDec 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

6,824625
Grand MasterGrand Master
6,824625

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

14
Basic UserBasic User
14

PostDec 13, 2020#6

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

PostMay 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,824625
Grand MasterGrand Master
6,824625

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

14
Basic UserBasic User
14

PostMay 26, 2021#9

Thanks a lot. I can also use:

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

PostJun 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,824625
Grand MasterGrand Master
6,824625

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

14
Basic UserBasic User
14

PostJun 04, 2021#12

Thank you.

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

19276
MasterMaster
19276

PostJun 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

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

19276
MasterMaster
19276

PostJun 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)