Find and replace consecutive numbers
 

Hi All

I have two columns J and K of text and numbers.

I need to find numbers down from 50 followed by a full stop

(50. , 49. , 48. , 47. Etc)

and replace with

<p50 , <p49 , and so on.

No full stop in the replace string.

I did manage to do this long-hand , replacing each one by one , only for
Excel to tell me that my procedure was now too long.

Can someone help with some neat VBA to do this? I'm grateful for any
help on this.




Best Wishes


Colin

Find and replace consecutive numbers
 

Using a copy of your data.........try just sorting to get them all together,
then highlight them and do Exit Replace, and replace the period, with
nothing, leave the box empty, then ReplaceAll........

If it is necessary to return to the original sorting, the before doing the
above, use a helper column to put sequentioal numbers in and after the above
procedure, sort on the new helper column, then delete it if deisred.

Vaya con Dios,
Chuck, CABGx3




"Colin Hayes" wrote in message
...


Hi All

I have two columns J and K of text and numbers.

I need to find numbers down from 50 followed by a full stop

(50. , 49. , 48. , 47. Etc)

and replace with

<p50 , <p49 , and so on.

No full stop in the replace string.

I did manage to do this long-hand , replacing each one by one , only for
Excel to tell me that my procedure was now too long.

Can someone help with some neat VBA to do this? I'm grateful for any
help on this.




Best Wishes


Colin

Find and replace consecutive numbers
 

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Colin Hayes" wrote:



Hi All

I have two columns J and K of text and numbers.

I need to find numbers down from 50 followed by a full stop

(50. , 49. , 48. , 47. Etc)

and replace with

<p50 , <p49 , and so on.

No full stop in the replace string.

I did manage to do this long-hand , replacing each one by one , only for
Excel to tell me that my procedure was now too long.

Can someone help with some neat VBA to do this? I'm grateful for any
help on this.




Best Wishes


Colin

Find and replace consecutive numbers
 

On Mon, 25 Dec 2006 23:25:47 +0000, Colin Hayes
wrote:



Hi All

I have two columns J and K of text and numbers.

I need to find numbers down from 50 followed by a full stop

(50. , 49. , 48. , 47. Etc)

and replace with

<p50 , <p49 , and so on.

No full stop in the replace string.

I did manage to do this long-hand , replacing each one by one , only for
Excel to tell me that my procedure was now too long.

Can someone help with some neat VBA to do this? I'm grateful for any
help on this.




Best Wishes


Colin

What does your data really look like? In other words, what, exactly is in the
cell and is it formatted as text or as a number?

Or do you have comma separated values in the cells as you show in your example?
--ron

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

Find and replace consecutive numbers
 

Colin,

You can try text to columns on the data menu.
Otherwise it is a matter of removing the witespace .

This can be done with VBA where you read lines of data and transform it. It
really depends on, as Ron said, what your data or file looks like. I assume
that you are trying to make some type of HTML page with the <p.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Colin Hayes" wrote:

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

Find and replace consecutive numbers
 

In article , Martin
Fishlock writes
Colin,

You can try text to columns on the data menu.
Otherwise it is a matter of removing the witespace .

This can be done with VBA where you read lines of data and transform it. It
really depends on, as Ron said, what your data or file looks like. I assume
that you are trying to make some type of HTML page with the <p.



Hi Martin

Well it's the usual compendium , I'm afraid. Carriage returns ,
paragraph marks of all descriptions - many not even visible. It's a real
headache. I was hoping there was a simple remove-all procedure I could
build into the VBA. Oh well.


BTW in the coding you wrote :

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


This works perfectly for me , but I was wondering if it was possible to
have an either / or element in the selection.replace line.

At the moment it's checking to see if the number is followed by a full
stop , and then replacing with <p and the number.

Could this be modified so that


if the number is followed by a full stop

OR

the number is followed by a space


then replace this <p and the number?


I hope that makes sense. I'm grateful for any help on this.



Best Wishes


Colin