Community
    • Login

    Replace numbers single and two digit numbers in text string

    Scheduled Pinned Locked Moved Help wanted · · · – – – · · ·
    8 Posts 3 Posters 1.1k Views
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • Otniel UlloaO
      Otniel Ulloa
      last edited by

      Hello
      I have xml files that contain the following

      ‘Data Entry’!Y9
      ‘Data Entry’!Z9
      ‘Data Entry’!AA9
      ‘Data Entry’!AF9
      ‘Data Entry’!A10
      ‘Data Entry’!AA10
      etc.

      I’d like to find and replace as following
      ‘Data Entry’!Y9 --> ‘Data Entry’!Y11
      ‘Data Entry’!AA9 --> ‘Data Entry’!AA11
      ‘Data Entry’!AA10 --> ‘Data Entry’!AA11

      they will all go from A9 or AA9 or A10, etc --> A11, AA11 or A11. Always ending in 11

      Thanks

      PeterJonesP 1 Reply Last reply Reply Quote 0
      • PeterJonesP
        PeterJones @Otniel Ulloa
        last edited by

        @Otniel-Ulloa ,

        What other data is in your data set which would get in the way of things that you want to match and replace will affect the regex (regular expression for search-and-replace).

        Since your example includes smart quotes ‘’, I am guessing that you actually typed ASCII single-quotes ', but the forum messed them up. But since I’m not sure, I won’t include the ‘Data Entry’ prefix in the regex. Follow the advice at the end of this post if you want better answers.

        But based on your example, I assumed that I’m just looking for exclamation point followed by 1 or more letters followed by one or more digits. In the replacement, I keep the exclamation point and the letters, and replace the numbers with 11.

        • Find What = (![[:alpha:]]+)\d+
          Store the ! and letter(s) in group 1, and eat the digits
        • Replace With = ${1}11
          Replace with the contents of group one, followed by a literal 11
        • Search Mode = Regular Expression

        Your example was not formatted using the forum’s formatting tools, and your example data didn’t show anything that shouldn’t match, so it’s hard to get a good regex that will actually make you happy. If you want a better search and replace than what I gave you, you are going to have to read, understand, and follow the advice below.

        ----

        Do you want regex search/replace help? Then please be patient and polite, show some effort, and be willing to learn; answer questions and requests for clarification that are made of you. All example text should be marked as plain text using the </> toolbar button or manual Markdown syntax. Screenshots can be pasted from the clipboard to your post using Ctrl+V to show graphical items, but any text should be included as literal text in your post so we can easily copy/paste your data. Show the data you have and the text you want to get from that data; include examples of things that should match and be transformed, and things that don’t match and should be left alone; show edge cases and make sure you examples are as varied as your real data. Show the regex you already tried, and why you thought it should work; tell us what’s wrong with what you do get… Read the official NPP Searching / Regex docs and the forum’s Regular Expression FAQ. If you follow these guidelines, you’re much more likely to get helpful replies that solve your problem in the shortest number of tries.

        1 Reply Last reply Reply Quote 2
        • Olivier ThomasO
          Olivier Thomas
          last edited by

          or:
          Find What = \d+$
          Replace With = 11

          1 Reply Last reply Reply Quote 0
          • Otniel UlloaO
            Otniel Ulloa
            last edited by

            Thank you guys for your response and question.

            Peter, your solution seems to work well.

            I do have an additional request.
            In this data set, is there a way to exclude ‘Data Entry’!Q3 from being selected during replacement?
            Specifically when it ends in 3

            Thanks

            My data is in .xml.

            Here’s a more representative sample of my data.
            x14ac:dyDescent=“0.25”><c r=“A26” s=“49”><f>‘Data Entry’!Q3</f><v>05255</v></c><c r=“B26” s=“49”><f>‘Data Entry’!R4</f><v>0</v></c><c r=“C26” s=“49”><f>‘Data Entry’!S4</f><v>1150</v></c><c r=“D26” s=“49”><f>‘Data Entry’!T4><v></v></c><c r=“B72” s=“213”/><c r=“C72” s=“213”/><c r=“D72” s=“213”/><c r=“E72” s=“213”/><c r=“F72” s=“225”><f>‘Data Entry’!Y9</f><v></v></c><c r=“G72” s=“225”><f>‘Data Entry’!Z9</f><v></v></c><c r=“H72” s=“49”><f>G72-F72</f><v>99.9999999999955</v></c><c r=“I72” s=“91”/></row><row r=“73” spans=“1:14” x14ac:dyDescent=“0.25”><c r=“A73” s=“213” t=“s”><v></v></c><c r=“B73” s=“213”/><c r=“C73” s=“213”/><c r=“D73” s=“213”/><c r=“E73” s=“213”/><c r=“F73” s=“225”><f>‘Data Entry’!AA9</f><v>8819999999999999</v></c><c r=“G73” s=“225”><f>‘Data Entry’!AB9</f><v>89864646</v></c><c r=“H73” s=“49”><f>G73-F73</f><v>99.00000000002</v></c><c r=“I73” s=“91” t=“s”><v></v></c></row><row r=“74” spans=“1:14” x14ac:dyDescent=“0.25”><c r=“A74” s=“92”/><c r=“I74” s=“91”/></row><row r=“75” spans=“1:14” x14ac:dyDescent=“0.25”><c r=“A75” s=“92”/><c r=“I75” s=“91”/></row><row r=“76” spans=“1:14” x14ac:dyDescent=“0.25”><c r=“A76” s=“92”/><c r=“I76” s=“91”/></row><row r=“77” spans=“1:14” x14ac:dyDescent=“0.25”><c r=“F77” s=“192”/><c r=“I77” s=“91”/><c r=“J77” s=“195” t=“s”><v>0000199</v></c><c r=“K77” s=“220” t=“s”><v>200</v></c><c r=“L77” s=“220”/><c r=“M77” s=“195” t=“s”><v>201</v></c></row><row r=“78” spans=“1:14” ht=“30” x14ac:dyDescent=“0.25”><c r=“A78” s=“212” t=“s”><v>67</v></c><c r=“B78” s=“212”/><c r=“C78” s=“212”/><c r=“D78” s=“212”/><c r=“E78” s=“212”/><c r=“F78” s=“63” t=“s”><v>104</v></c><c r=“G78” s=“63” t=“s”><v>105</v></c><c r=“H78” s=“63” t=“s”><v>106</v></c><c r=“I78” s=“91”/><c r=“J78” s=“191”><f>H79</f><v>159</v></c><c r=“K78” s=“207”><f>SUM(H80:H81)</f><v>90000000000003</v></c><c r=“L78” s=“207”/><c r=“M78” s=“191”><f>J78-K78</f><v>99999999999966</v></c></row><row r=“79” spans=“1:14” x14ac:dyDescent=“0.25”><c r=“A79” s=“213” t=“s”><v>111</v></c><c r=“B79” s=“213”/><c r=“C79” s=“213”/><c r=“D79” s=“213”/><c r=“E79” s=“213”/><c r=“F79” s=“225”><f>‘Data Entry’!AC9</f><v></v></c><c r=“G79” s=“225”><f>‘Data Entry’!AD9</f><v></v></c><c r=“H79” s=“49”><f>G79-F79</f><v>159</v></c></row><row r=“80” spans=“1:14” x14ac:dyDescent=“0.25”><c r=“A80” s=“213” t=“s”><v>112</v></c><c r=“B80” s=“213”/><c r=“C80” s=“213”/><c r=“D80” s=“213”/><c r=“E80” s=“213”/><c r=“F80” s=“225”><f>‘Data Entry’!AE9</f><v></v></c><c r=“G80” s=“225”><f>‘Data Entry’!AF9</f><v></v></c><c r=“H80” s=“49”><f t=“shared” ref=“H80:H82” si=“6”>G80-F80</f><v>99999999999989</v></c><c r=“I80” s=“91” t=“s”><v>113</v></c></row><row r=“81” spans=“1:10” x14ac:dyDescent=“0.25”><c r=“A81” s=“213” t=“s”><v>114</v></c><c r=“B81” s=“213”/><c r=“C81” s=“213”/><c r=“D81” s=“213”/><c r=“E81” s=“213”/><c r=“F81” s=“225”><f>‘Data Entry’!AG9</f><v></v></c><c r=“G81” s=“225”><f>‘Data Entry’!AH9</f><v>70000000000005</v></c><c r=“H81” s=“49”><f t=“shared” si=“6”/><v>0000000000045</v></c><c r=“I81” s=“” t=“s”><v>115</v></c></row><row r=“82” spans=“1:10” x14ac:dyDescent=“0.25”><c r=“A82” s=“213” t=“s”><v>116</v></c><c r=“B82” s=“213”/><c r=“C82” s=“213”/><c r=“D82” s=“213”/><c r=“E82” s=“213”/><c r=“F82” s=“224”><f>‘Data Entry’!AI9</f><v></v></c><c r=“G82” s=“224”><f>‘Data Entry’!AJ9</f><v></v></c><c r=“H82” s=“49”><f t=“shared” si=“6”/><v></v></c><c r=“I82” s=“91” t=“s”><v>117</v></c></row><row r=“83” spans=“1:10” x14ac:dyDescent=“”><c r=“H83” s=“91”/></row><row

            PeterJonesP 1 Reply Last reply Reply Quote 0
            • PeterJonesP
              PeterJones @Otniel Ulloa
              last edited by PeterJones

              @Otniel-Ulloa said in Replace numbers single and two digit numbers in text string:

              Specifically when it ends in 3

              Once again, not very precise in what you’re asking for. Do you mean “any that ends in three” (like Q3, Z13, FF123), or do you mean “the numerical portion is exactly 3” (like Q3, ZZ3, but not Z13 or FF123)

              If you cannot understand your data well enough to express it in terms that don’t allow for confusion, then you cannot get a good regular expression that will work reliably for you.

              Further, you did not follow my advice – you did not use the </> button on the toolbar to format your example text.
              a1eb80a2-11e3-4b95-b704-2e352ab424e5-image.png
              By not reading and understanding my advice, you are proving that you aren’t willing to help us help you… which means we will be less likely to be able to help you.

              This forum is not a “I can come here and ask for someone else to do all the hard work of writing a regex for me”. We will often help a new user through the first one or two regex, but we do expect them to listen to our advice and try to read the documents and figure out regex for themselves.

              I will give two more regex for you in this post – one assuming “exactly 3” and the other assuming “ends in 3” – but after that, if you want better help, you have to get better at specifying what you actually want, and you have to get better at making it easy for us to help you.

              “specifically when it ends in exactly 3”:

              • find what = (![[:alpha:]]+)([012456789]\b|\d{2,}\b)
                • the first part is the same as before
                • the digit matching was changed to match any single digit but 3, or any combination of 2 or more digits (even if it is a 3). The \b is a “boundary” assertion, saying that what comes after the last digit cannot be another digit or letter.

              "specifically when it ends in any number of other digits followed by a 3:

              • find what = (![[:alpha:]]+)(\d*[012456789]\b)
                • the first part is the same as before
                • the second part now requires 0 or more digits followed by a digit that is not 3, followed by a boundary.

              Remember, if you want better help, you need to show a willingness to learn and to follow advice. Good luck.

              1 Reply Last reply Reply Quote 4
              • Olivier ThomasO
                Olivier Thomas
                last edited by

                or
                find what = (?<=!\w)([0124-9])(?=<)
                Replace With = 11

                1 Reply Last reply Reply Quote 0
                • Olivier ThomasO
                  Olivier Thomas
                  last edited by Olivier Thomas

                  (?:!\w+)\K([0124-9])(?=<)

                  There is a bug
                  <f>‘Data Entry’!T4><v>
                  4>

                  1 Reply Last reply Reply Quote 0
                  • Otniel UlloaO
                    Otniel Ulloa
                    last edited by

                    Thanks guys.

                    Yes, this is my first time using regular expressions and it’s going to take me some time to understand it.

                    This will get me started though.

                    Thanks

                    1 Reply Last reply Reply Quote 0
                    • First post
                      Last post
                    The Community of users of the Notepad++ text editor.
                    Powered by NodeBB | Contributors