Replace numbers single and two digit numbers in text string
-
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’!AA11they will all go from A9 or AA9 or A10, etc --> A11, AA11 or A11. Always ending in 11
Thanks
-
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 literal11 - 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 usingCtrl+Vto 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. - Find What =
-
or:
Find What =\d+$
Replace With =11 -
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 3Thanks
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 -
@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.
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
\bis 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.
- find what =
-
or
find what =(?<=!\w)([0124-9])(?=<)
Replace With =11 -
(?:!\w+)\K([0124-9])(?=<)There is a bug
<f>‘Data Entry’!T4><v>
4> -
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
