nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Delete text substring from longer text

DanielBrenner

I have a field in a .dbf file with a long text string. I need to delete x amount of characters WITHIN the text string. I am also not very good with programming so I haven't looked into creating a script.

Essentially I have this:

aaa bbb ccc ddd eee fff ggg

What I need is this:

aaa bbb ccc fff ggg

I'm attaching the .dbf in MS Excel format (.xls). I have had some luck with functions in MS Excel but I can't get the consistency I need.

Naplabels.xlsx

Vind meer berichten getagd met

Accepted answers

All comments

StephenM

Maybe you could use regular expressions to replace substrings based on the character index of the first number in the ###.### sequence. Get the index of that character, then use string slices to replace all text between the third character (assuming all the rows start with "NAP") and that index.

Example in ArcGIS Pro using Python:

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">replace_substr</SPAN><SPAN class="punctuation token">(</SPAN>s<SPAN class="punctuation token">,</SPAN> rte<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
    <SPAN class="keyword token">import</SPAN> re

    <SPAN class="comment token"># Set pattern to replace beginning section. Optional</SPAN>
    <SPAN class="comment token"># L or R and a space, then 3 numbers, a period, three</SPAN>
    <SPAN class="comment token"># numbers, a space, 1 or 2 letters, and a space.</SPAN>
    ptn <SPAN class="operator token">=</SPAN> re<SPAN class="punctuation token">.</SPAN>compile<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"([LR] )?[0-9]{3}.[0-9]{3} [A-Z]{1,2} "</SPAN><SPAN class="punctuation token">)</SPAN>
    
    <SPAN class="comment token"># Match the pattern in the string</SPAN>
    match <SPAN class="operator token">=</SPAN> re<SPAN class="punctuation token">.</SPAN>finditer<SPAN class="punctuation token">(</SPAN>ptn<SPAN class="punctuation token">,</SPAN> s<SPAN class="punctuation token">)</SPAN>
    
    <SPAN class="comment token"># Get the first match</SPAN>
    m <SPAN class="operator token">=</SPAN> next<SPAN class="punctuation token">(</SPAN>match<SPAN class="punctuation token">)</SPAN>
    
    <SPAN class="comment token"># Get the index where the match started</SPAN>
    idx <SPAN class="operator token">=</SPAN> m<SPAN class="punctuation token">.</SPAN>start<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
    
    <SPAN class="comment token"># Create new string by concatenating string slices and route</SPAN>
    new_str <SPAN class="operator token">=</SPAN> s<SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">:</SPAN><SPAN class="number token">3</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">+</SPAN> rte <SPAN class="operator token">+</SPAN> s<SPAN class="punctuation token">[</SPAN>idx<SPAN class="punctuation token">:</SPAN><SPAN class="punctuation token">]</SPAN>
    
    <SPAN class="comment token"># Set pattern to replace the 'DH ##.###' section</SPAN>
    ptn <SPAN class="operator token">=</SPAN> re<SPAN class="punctuation token">.</SPAN>compile<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"DH [0-9]{2}.[0-9]{3}"</SPAN><SPAN class="punctuation token">)</SPAN>
    
    <SPAN class="comment token"># Replace the 'DH ##.###' section with nothing</SPAN>
    new_str <SPAN class="operator token">=</SPAN> re<SPAN class="punctuation token">.</SPAN>sub<SPAN class="punctuation token">(</SPAN><SPAN class="string token">" +"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">" "</SPAN><SPAN class="punctuation token">,</SPAN> re<SPAN class="punctuation token">.</SPAN>sub<SPAN class="punctuation token">(</SPAN>ptn<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">""</SPAN><SPAN class="punctuation token">,</SPAN> new_str<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
    
    <SPAN class="keyword token">return</SPAN> new_str‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

table showing original strings and strings after replacement using regular expressions

DanielBrenner

Guys,

See attached and also refer to the other stuff.

The deal is is that NAP ‘xyz’ can a 3-4 letter character string or a space between spaces. It’s the variability of what needs to be changed to the even bigger variability in terms of the number of text strings that could go in front of the space between the 3 -4 character string or the space AND the R or L or other space before a 6 digit string representing a number.

I did come up with a partial solution, where I created a text string in MS Excel with a dash before and after the route number; this text string would replace the 2nd set of characters, but again, the variability of the size of the string is a kicker.

How does any of what you guys are suggesting deal with this?

Yes it would cover for some of what the bigger formula was getting at with the “ -121- “ part, but again, not one string fits all. I even had to edit 29 to become 029 because it wasn’t in a consistent 3 digit format like the other route.

The second string is nearly out the window in terms of variability of starting position and numbers of characters. But again, I didn’t have a problem editing the characters and the text strings as need be.

Seriously I do thank you for this.

Daniel T. Brenner

RESEARCH ANALYST I (GEOGRAPHIC INFORMATION SYSTEMS)

DIVISION OF RESEARCH, INNOVATION, and SYSTEM INFORMATION

Highway System Information and Performance

1120 N Street MS 38

Sacramento, CA 95814

(916) 657-4277

(510) 593-7547 cell

daniel.brenner@dot.ca.gov<mailto:daniel.brenner@dot.ca.gov>

“Consider the unconsidered.”

“Certainty is not a sure thing!”

Rtenumber.xlsx

Naperr.jpg

JoshuaBixby

I agree with the other respondent from the different forum. The crux of your automation/scripting problem lies in:

Edited the "NAP" text string as need be to represent the correct character string (or spaces) as need be

You know what is correct "as need be," but you don't seem to be able to write out a rule set for it. If you are changing your script/formula every few rows, and not relying on a per-defined rule set of what needs to get changed to what, then you might as well just do it by hand.

DanielBrenner

Joshua,

See attached.

Its based on how the original text string describes a point location on a certain highway number network (like those little sticks along the side of the highway that have County-Route-Postmile stenciled on them) that’s in another field.

I did discuss the same matter with another respondent from a different forum and he was saying that the text strings couldn’t be created with out a logical scenario (IF location = x then label with Y) and I thought of using a drop down list of all the routes, and even joining them by another attribute, but all of that was going to be too much and given that I was going to be using the points layer’s dbf file in MS Excel I was going to be able to see the route number for the point location that needed the text string I’m trying to create and so it was just a matter of substituting when I had to.

Someone else emailed about using Data Dictionaries and I saw how they essentially did the same thing but the advantage for MS Excel is that I wouldn’t have had to include every record’s substring as part of one of them, and that the code to use was way simpler.

Hope this helps.

Daniel T. Brenner

RESEARCH ANALYST I (GEOGRAPHIC INFORMATION SYSTEMS)

DIVISION OF RESEARCH, INNOVATION, and SYSTEM INFORMATION

Highway System Information and Performance

1120 N Street MS 38

Sacramento, CA 95814

(916) 657-4277

(510) 593-7547 cell

daniel.brenner@dot.ca.gov<mailto:daniel.brenner@dot.ca.gov>

“Consider the unconsidered.”

“Certainty is not a sure thing!”

NapLabelsEDITstring.jpg

JoshuaBixby

I don't understand what criteria/condition is driving the replacement text. For example, when

NAP NAP R 004.501 DH 00.334 VIA IMOLA AVE
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

gets changed to

NAP-121-R 004.501 DH 00.334 VIA IMOLA AVE
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

, " NAP " is getting changed to "-121-" .

However, when

NAP NAP 010.698 DH 00.501 OLD SONOMA RD OC 21 87
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

gets changed to

NAP-029-010.698  OLD SONOMA RD OC 21 87
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

, " NAP " is getting changed to "-029-".

How are you deciding when " NAP " gets changed to one value versus another?

DanielBrenner

Yes there are a lot of different values I need to replace of varying lengths (see the parts of the text strings beginning with ‘DH’ or the one with ‘E DH’….)

I haven’t worked with Python Dictionaries. Could you give me an example of what you are talking about?

StephenM

Are there a lot of different substrings you want to replace? I'm thinking maybe you could use a Python dictionary, but that would require explicit definition of substrings and their replacements. Since the substrings aren't always replaced with the same text, you'd also have to have some sort of decision-making in the code.

DanielBrenner

Stephen,

Thanks for your efforts but I have been married to MS Excel for the last 4 days!!!

The main reason I was posting on the ArcGIS Forum is that I wanted to hope beyond all hope that maybe there is a VBA or Python script out there that would do what I’m trying to do in MS Excel?

Sheet 2 of Naplables0403 is closer to what I am wanting. I can do nested SUBSTITUTE functions to replace the text strings but I can’t get one set of SUBSTITUTE functions to repeat because somehow its text-string specific. i.e., DH 00.334 can only be deleted from the first text string, but even if I put an instance number of 2 (where I’m hoping the second string can be replaced), it doesn’t do that—it only replaces the text its referring to!

Perhaps a better way of putting this is how can I replace substrings of text of varying lengths with spaces between varying locations of spaces??

As in:

Aaa bbb ccc ddd eee fff

Would result in

Aaa bbb eee fff

But then

AAA BBB CCC DD EEE FF GGG

Could result in

AAA BBB CCC FF GG

Daniel T. Brenner

RESEARCH ANALYST I (GEOGRAPHIC INFORMATION SYSTEMS)

DIVISION OF RESEARCH, INNOVATION, and SYSTEM INFORMATION

Highway System Information and Performance

1120 N Street MS 38

Sacramento, CA 95814

(916) 657-4277

(510) 593-7547 cell

daniel.brenner@dot.ca.gov<mailto:daniel.brenner@dot.ca.gov>

“Consider the unconsidered.”

“Certainty is not a sure thing!”

Naplabels0403.xlsx

StephenM

The REPLACE function might work for this:

Excel formula: Remove text by position

Original text	Formula	New text
NAP NAP 012.039 DH 00.449 LINCOLN AVE OC 21-89	=REPLACE(A1,4,12,"")	NAP DH 00.449 LINCOLN AVE OC 21-89

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.