Replace Values In Excel

[jbsoccerbrit]

I am just curious if there was a way to remove from an excel sheet any numerical values followed by a period.

For example

1. open application
2. login to application

Would become

Open application
Login to application

I've looked at regular expressions and don't fully understand well enough how to use them to do such a task. I've also tried making something that would have a low and high value in a gui but it seems like overkill to do such a job.

Any help would be greatly appreciated. 

TestCaseCleaner.au3

[JoHanatCent]

Did you try the Excel Management part in help?

[water]

Seems Excel VBA is capable of regular expressions but unfortunately this can't be used by COM.
https://excel.tips.net/T003303_Wildcards_in_Replace_With_Text.html

[Juvigy]

Yes it is possible and very easy. Try this:

Worksheets("Sheet1").Columns("A").Replace _ 
 What:="1.", Replacement:="", _ 
 SearchOrder:=xlByColumns, MatchCase:=True

You can put that in a loop and do all numbers from 1 to 100 for example.

[AutoBert]

4 minutes ago, Juvigy said:

Yes it is possible and very easy.

For one replacement correct, if putting in a loop strange results are possible.

[jbsoccerbrit]

10 hours ago, Juvigy said:

Yes it is possible and very easy. Try this:

Worksheets("Sheet1").Columns("A").Replace _ 
 What:="1.", Replacement:="", _ 
 SearchOrder:=xlByColumns, MatchCase:=True

You can put that in a loop and do all numbers from 1 to 100 for example.

Thank you, could you possibly provide an example of a loop for this?

[water]

Adding a loop isn't too complex. Check the help file index for "Loop" and you know where to start Try it yourself and post any questions if you have problems (including the code).

[Juvigy]

I am guessing you dont know that that was VBA so let me show you how it looks in autoit:

For $i=1 to 100 step 1
    $oExcel.Application.Workbooks("Accounts.xlsx").ActiveSheet.UsedRange.Replace($i&".","")
Next

 

[water]

To enhance performance you could use:

Local $oUsedRange = $oExcel.Application.Workbooks("Accounts.xlsx").ActiveSheet.UsedRange
For $i=1 to 100 step 1
    $oUsedRange.Replace($i&".","")
Next