Jump to content

Recommended Posts

Posted

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

Posted

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

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.

Posted
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?

Posted

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).

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

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

 

Posted

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

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...