Find & Replace formula for numbers?

Griff · #1 04-17-2011, 09:19 PM

Is there a formula for Find & Replace? Specifically for replacing numbers with other numbers?

I have a document with 11 pages of various number combinations that look a bit like this:

A-1, 6, 7, 8
A-3, 8, 1, 2
A-5, 2, 3, 4
A-7, 4, 5, 6

I need to change all the instances of...
"1" to "8"
"2" to "1"
"3" to "2"
"4" to "3"
And so on...

I would do a simple Find & Replace, but by the time I get to the final one (which is changing "8" to "7") the Find function will locate all the "8" including the ones which used to be "1". Get me?

So if there were a function or formula that could just subtract 1 from the value I'd be set.

macropod · #2 04-18-2011, 12:31 AM

Hi Griff,

Try the following macro:

Code:

Sub Renumber()
Dim i As Long
For i = 1 To Selection.Words.Count
  With Selection.Words(i)
    If IsNumeric(.Text) Then
      .Text = (CLng(.Text) + 6) Mod 8 + 1
    End If
  End With
Next
End Sub

Griff · #3 04-18-2011, 01:13 AM

Awesome, thanks!

macropod · #4 04-18-2011, 01:43 AM

Hi Griff,

I just realised your profile says you're using Office 2008 ... which doesn't support macros. The same result could be achieved with a series of Find/Replace operations:
Find = 1
Replace = 0
Find = 2
Replace = 1
Find = 3
Replace = 2
Find = 4
Replace = 3
Find = 5
Replace = 4
Find = 6
Replace = 5
Find = 7
Replace = 6
Find = 8
Replace = 7
Find = 0
Replace = 8

Griff · #5 04-18-2011, 02:47 AM

Oh bummer...

Oh, but that new solution looks like a perfect method. I hadn't even thought of "temporarily" changing one of the numbers to something else (0). That's great! Thank you!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Help with find and replace or query and replace	shabbaranks	Excel	4	03-19-2011 08:38 AM
Find and replace page numbers in body of text	tollanarama	Word	3	02-13-2011 06:00 AM
replace automatically generated page numbers	LitWissOnline	Word	3	01-24-2011 03:40 PM
find and replace	anil3b2	Word	0	08-03-2010 11:30 PM
Find /Replace Help	ilcaa	Word	2	11-02-2009 08:41 AM