View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Colin Hayes Colin Hayes is offline
external usenet poster
 
Posts: 465
Default Find and replace consecutive numbers

In article , Martin
Fishlock writes
Colin

Try this code, using the selection for the range.

Sub ReplaceNumbers()
Dim i As Long
For i = 50 To 1 Step -1
Selection.Replace What:=i & ".", _
Replacement:="<p" & i, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next i
End Sub


Hi Martin

Yes , that's fixed it - thanks! Hooray!

My macro is not running out of space now as it's such a neat piece of
code.

It's brought up a related issue though - to do with formatting marks.
Because I'm pasting text to excel , I'm finding that the text has all
sorts of format characters (returns , paragraph marks etc). I've tried
all sorts of search and replace.

Is there any way of running a command to strip these all out , leaving
nice clean text? Or some sort of copy and paste? I know this can be done
in Word , but don't see a way in Excel.

Hope you can help.



Best Wishes


Colin