how do I replace string 123-4 with 123 4

davidjm · #1 10-29-2021, 10:05 AM

Hi,
I have a word doc and some vb code that increments all numeric values by 2, so for example value 123-4 should return 125-6, but what I receive is 125-4. I tried :
text = Replace(text, "-", " ") but the text value doesn't change. Replacing other values works as expected.
How can I replace '-' between 2 numbers with space so that both numbers are incremented.
This is part of an index I'm trying to update, but the index was created manually and not using any of the Word indexing options
Any help much appreciated

rollis13 · #2 10-29-2021, 01:26 PM

How about showing your vb code that increments all numeric values by 2 ... maybe ...

davidjm · #3 10-29-2021, 04:58 PM

Here's the code:

Code:

Sub perLine()
' Loops thru lines in document passing each line to following Sub ModifyNumbersInText(line)
Static objRegex As Object
Static matches As Object
    If objRegex Is Nothing Then Set objRegex = CreateObject("VBScript.RegExp")
Dim strSample As String

 Dim para As Paragraph
    Dim sentence() As String
    For Each para In ActiveDocument.Paragraphs
          sentence() = Split(para.Range.Text, Chr(11))
            For i = 0 To UBound(sentence)
                  strSample = sentence(i)                  
                      With objRegex
                       .Pattern = "(\-)(\d)"
                       .Global = True
                       .IgnoreCase = True
                       strSample = .Replace(strSample, " X $2")
                      End With
                  ModifyNumbersInText (strSample)
            Next i
    Next
End Sub

Sub ModifyNumbersInText(line)
'Adds 2 to all numerical values in line

Dim txt As String
Dim NumbersFound As String
Dim NumberArray As Variant
txt = line

'Use Regular Expressions to pull out numbers
  Set objRegex = CreateObject("VBScript.RegExp")
  objRegex.IgnoreCase = True
  objRegex.Global = True
  
  objRegex.Pattern = "\d+" 'grab one or more digits
  
  If objRegex.Test(txt) Then
    Set Result = objRegex.Execute(txt)
      If Result.Count > 0 Then
        For Each match In Result
          NumbersFound = NumbersFound & ";" & match.Value
        Next match
    Else
        Exit Sub
      End If
  End If

'Create Array out of Numbers Found
  NumbersFound = Right(NumbersFound, Len(NumbersFound) - 1)
  NumberArray = Split(NumbersFound, ";")
  
'Find/Replace (add 2)
  For x = LBound(NumberArray) To UBound(NumberArray)
    If NumberArray(x) > 30 Then
        txt = Replace(txt, NumberArray(x), NumberArray(x) + 2)
    End If
  Next x

'Output result
    Debug.Print txt
End Sub

This code is mostly tagged together from googling, but it all works apart from numbers pre-pended with "-". When I run

Code:

                  strSample = sentence(i)                  
                      With objRegex
                       .Pattern = "(\-)(\d)"
                       .Global = True
                       .IgnoreCase = True
                       strSample = .Replace(strSample, " X $2")
                      End With

on individual lines it replaces "-" followed by digit with " X " followed by digit.
Running Sub perLine() doesn't do the Regex replace above.

Snippet from Word document that I'm trying to update:

adaptive-expectations hypothesis 261–2, 305
aggregate demand 232–3, 305
aggregate supply 234–7, 305
aggregate supply curve 231
perfectly elastic 235
perfectly inelastic 235–6
aggregates levy 169

Thanks for any help

Guessed · #4 10-29-2021, 07:46 PM

This looks somewhat complicated.

Are you trying to change page numbers for elements?
Like what is meant to happen if the last digit is an 8 or 9?
238-9 becomes 230-1 OR 240-1 OR 240-11
237-9 becomes 239-1 OR 239-41 OR 239-11

davidjm · #5 10-29-2021, 09:14 PM

Hi Andrew,
Thanks for the reply.
This is an index that's had a couple of pages added so page 123-4 becomes 125-6. My problem at the moment is that 123-4 becomes 125-4. Page numbers following "-" are not changing. This is the main problem that I want to solve.
Thanks for any help

Guessed · #6 10-29-2021, 09:44 PM

Try this code. Note that the hyphen isn't a plain hyphen - it is an n-dash which has an Asc value of 150. A regular hyphen has an Asc value of 45.

Code:

Sub FindPattern()
  Dim aRng As Range, str As String, int1 As Integer, int2 As Integer, arrStr() As String
  Set aRng = ActiveDocument.Range
  With aRng.Find
    .ClearFormatting
    .MatchWildcards = True
    .Text = "[0-9]{3}–[0-9]{1}"
    Do While .Execute = True
      arrStr = Split(aRng.Text, "–")
      int1 = CInt(arrStr(0)) + 2
      int2 = CInt(arrStr(1)) + 2
      aRng.Text = int1 & "-" & int2
      aRng.Collapse Direction:=wdCollapseEnd
      aRng.End = ActiveDocument.Range.End
    Loop
  End With
End Sub

I note you didn't answer the question of what happens with the last number if it is an 8 or 9 so I didn't make an effort to handle that case differently.

davidjm · #7 10-29-2021, 10:44 PM

Hi Andrew,
Thanks very much for this, going to give it a try - I'll let you know.
Think that difference between n-dash and hyphen is what I was missing, although don't see where you've specified the type of dash (no Asc values).
To complete this code also need to include 1 - 3 digit numbers.
I'm dealing with 1 problem at a time, the current index has for example 129-1 - not ideal but understandable

davidjm · #8 10-30-2021, 03:11 AM

Hi Andrew,
Thanks very much for taking the time to do this, much appreciated.
Huge advance and I've learned alot more from this than Googling in the dark.
I've noticed there's a mixture of formats for page ranges as you can see from below:

Before .................................................. ........ After
average variable (AVC) 108, 305 < > average variable (AVC) 108, 305
external 41–3, 164–5, 175, 186–8 < > external 41–3, 166-7, 175, 188-10
long run 113–14 < > long run 115-34
marginal (MC) 105–6, 110 < > marginal (MC) 107-8, 110
private 41–2, 186 < > private 41–2, 186
short run 106–9 < > short run 108-11
social 186–7 < > social 188-9
credit 16, 205, 206, 215, 258–61, 308 < > credit 16, 205, 206, 215, 260-81, 308

If there's for example 186-8 then After should be 188-0 (to keep the current format)
I have another question, your code includes

Code:

    .Text = "[0-9]{3}–[0-9]{1}"

This is a regex notation, but there's no mention of a RegExp object. How does that work?
Thanks for all your help

davidjm · #9 10-30-2021, 10:38 AM

Hi, trying to make this a bit more flexible.
Replaced

Code:

.Text = "[0-9]{3}–[0-9]{1}"

With

Code:

.Text = "[0-9]+–[0-9]+"

(this works in regex101: build, test, and debug regex)
but it's not working here, why is this?

As well as finding numeric strings with "-" I also need to find numeric strings from 1 - 999 does that mean running a second aRng.Find ?

Guessed · #10 10-30-2021, 08:08 PM

David
I have no idea of what you are trying to do with your numbers as you don't appear to want to change the individual pages but do want to change spans but not deal with numbers that click over a second digit. Then 113-14 needs to become 115-34?? That one makes no sense at all to me.

My code is using Word's built-in wildcard functionality. This is similar to regex but less powerful. If you want to search for a number with between 1 and 3 digits you look for "[0-9]{1,3}" so you are probably trying to find "[0-9]{1,3}-[0-9]{1,2}"

davidjm · #11 10-31-2021, 01:08 AM

Apologies for not being clear.

I was asked to update this index and noticed inconsistencies in the format of ranges eg. 128-0 & 128-30 and was trying to find the simplest way (for me) to deal with them, obviously 2 digits numbers after the dash is best.
Really just need to increment all the page numbers by 2, hope that makes more sense.
Thanks for any help

p.s.
Just realized another misunderstanding, the previous post I sent you highlighted in red the errors that were output from the script you sent and where not meant to indicate results that I expected - sorry for the confusion.

Guessed · #12 10-31-2021, 02:29 PM

This is taking so long because you are adding new conditions incrementally and haven't yet considered the rolling over of a third digit eg 88-90 or 99-101 or 200-9. Plus you haven't focused on the individual page numbers yet which would need to be addressed FIRST.

Ultimately, however this is just window-dressing and not actually addressing the real problem which is your document should actually be using the built-in index functionality instead of a manual index with hard coded numbers. Can you explain why this hasn't been your selected approach?

davidjm · #13 10-31-2021, 03:28 PM

Hi Andrew,
I understand that Word has built in indexing functionality, but I was presented with this index by someone who didn't use that, I'd like to help him and also learn a little VBA.

The full story is that pages 29-30 are new so any page number in the index > 28 need to be increased by 2, that includes individual page numbers and page references that span more than 1 page. I'm not experienced enough to decide what to do about the inconsistencies of the roll over digit, seems to me best to just keep the format as it is so
that 298-9 becomes 300-1 and 198-99 becomes 200-01, but I'm looking for the easiest coding solution. Your initial script was a great help and I'll use that as the base for continuing with this project, and appreciate any suggestions you have.
From what I've explained do you think that I can update the index with just Word's built-in wildcard functionality or do I need RexExp?
Thanks for all your help

davidjm · #14 10-31-2021, 04:51 PM

Hi,
working on this I've replaced

Code:

    .Text = "[0-9]{3}–[0-9]{1}"

with

Code:

     .Text = "[0-9]{2,3}–[0-9]{1,2}"

Which produces better results for references that span more than 1 page and keeps original format.

Was thinking if I just add 2 to any numeric string ("[0-9]{2,3}") regardless of if it was part of a range or an individual page number that would reduce the whole process to 1 .find . Is that correct? What would be the script
Thanks for any help

Guessed · #15 10-31-2021, 05:16 PM

You don't need RegEx. Identifying the numbers is not the hard part, working out what to do with them is the hard part.

I don't have time to work on this today so I will just give you some tips on how I would handle this.

The problem with spans (page ranges) is that there are understood numbers that could click over and add preceding digits. So I would prepare the trailing numbers by filling in the understood numbers before adding 2. For example
75 transforms to 77
99 transforms to 101
75-9 needs to become 75-79 before we add two to both to make them 77-81.
88-9 needs to become 88-89 then transform to 90-91 then an extra cleanup step to become 90-1
97-9 needs to become 97-99 then transform to 99-101
99-100 transforms to 101-102 and cleans up to 101-2

So the macro workflow should be:
1. Expand all 'understood' numbers to be the actual number by adding the missing digits
2. Add 2 to every number (whether in a span or not)
3. Simplify the spans where length and initial digits match

Look at the arrStr() elements in my earlier code. This gives you two strings for the spans, you can then test the Len("string") and use Left("string",1) to backfill a digit into the understood character position.

To include the spanning number patterns you actually have change this line in my original code
.Text = "[0-9]{1,4}–[0-9]{1,4}"

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Replace characters in a string	Marcia	Excel Programming	5	05-04-2020 05:15 AM
Wildcard replace any string in context with a specified string	wardw	Word	7	05-07-2018 09:13 AM
Replace multiple strings by one same string at once	puff	Word	2	02-28-2018 11:04 AM
Replace characters in a string	Anthon	Excel Programming	1	11-03-2016 12:48 AM
How to do multiple find and replace in string.	PRA007	Word VBA	2	01-06-2016 09:10 PM