how do I replace string 123-4 with 123 4

Guessed · #1 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 · #2 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 · #3 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 · #4 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 ?

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

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