topic Re: Dynamic Find and Replace in Macro in General Discussions

Dynamic Find and Replace in Macro

KieranRidge — Sat, 11 Jan 2025 21:33:36 GMT

Hello Alteryx Community,

It has been a long time since I posted. I'm back at batch macros and am pretty rusty on some of the less intuitive parts so I am turning here. I'm setting up a batch macro where I am running a Find and Replace for multiple different fields. Section1 is in both inputs and pulls in field Section1x, then Section2 is in both inputs and pulls in field Section2x, etc. I plan on doing this for dozens of fields at once. I overwrote the backend XML code pretty well but when I plug in the Text Input to feed the field names to run it on, the outputs get Unioned. E.g. I'll have 5 records in the input, but since I run the Find and Replace for three different fields, it runs the workflow thrice and yields 15 total records in the output. I think the solution might be the Group By option, but that appears not to be deduping the outputs (I'll also take suggestions to any good Group By tutorial refreshers for batch macros because again, I am rusty). Here's a screenshot of what the current output is vs. what I would like the output to be. I feel like this is something of a general question but I just can't figure it out for my particular workflow.

Also, subsequently off of that, does anyone know how to change it so I can just select the fields I want to run through the macro as a list on the macro interface? That would replace manually typing out the field names into the Text Input like I currently do. I think this would involve changing the Control Parameter to a List Box, but playing around with it isn't working. I've done it before as you see in a previous macro below, but unsure how to replicate in this scenario. YXZP file attached.

Thank you,
KPR

Re: Dynamic Find and Replace in Macro

Yoshiro_Fujimori — Sun, 12 Jan 2025 06:10:38 GMT

Hi @KieranRidge ,

I would get your desired output with Transpose / Cross Tab as attached (without Batch Macro).

The idea is to create a unique key for each value to replace, so that it can be replaced with one-shot.

I hope this helps.

Workflow

Formula #1

[Name] = Replace([Name], "section", "")

Formula #2

[section] = REGEX_Replace([Name], "\l", "")

[FindReplace] = IF EndsWith([Name], "x") THEN "Replace" ELSE "Find" ENDIF

Formula #3

[Name2] = IF [Name] = "Value" THEN "section" + [section] ELSE "section" + [section] + "x" ENDIF

Re: Dynamic Find and Replace in Macro

KieranRidge — Sun, 12 Jan 2025 15:58:58 GMT

Hi @Yoshiro_Fujimori - thanks, but I'm looking to do this in batch macro form as I am having some analysts take the workflow over from me who aren't as advanced, thus the intended batch macro would set it in such a way that they would only have to select the desired fields to run the Find and Replace up front. So essentially it's a process less advanced users are taking over, thus the front end wouldn't be built to be complex. Also, it looks like the workflow you attached doesn't match the screenshot.

Re: Dynamic Find and Replace in Macro

Yoshiro_Fujimori — Sun, 12 Jan 2025 22:55:50 GMT

Hi @KieranRidge ,

Your expected output

> it looks like the workflow you attached doesn't match the screenshot

I cannot find the mismatch. Could you point out where are they?

RecordID	section1	section2	section3	section1x	section2x	section3x
1	cd	abc	a	xx	gg	tyy
2	cd	acb	b	xx	bb	yw
3	cf	acb	a	yy	bb	tyy
4	cf	abc	b	yy	gg	yw
5	cf	ccc	c	yy	ff	np

Nature of Batch Macro

Batch macro processes the data fed to Control Parameter tool row by row.

So if the section names are fed to Batch macro with one column and multiple rows,

it will output the result of Find Replace for each [section] separately.

So I do not think batch macro can be a solution in your case.

(Though there may be a solution with batch macro, I cannot figure it out for now)

User Interface

You can make the workflow to Analytic App without Batch Macro as the attached sample.

Re: Dynamic Find and Replace in Macro

sonalsharmaqueen — Fri, 17 Jan 2025 08:38:21 GMT

Creating a dynamic find-and-replace macro involves automating the process of identifying text patterns or specific strings in a document or data source and replacing them with the desired values. This can be implemented in various programming environments, such as VBA (Visual Basic for Applications), Python, or other scripting tools.

Example: VBA Macro for Dynamic Find and Replace in Excel

Here's a VBA macro that dynamically performs a find-and-replace operation in an Excel sheet:

Steps to Use:

Open the Excel workbook.
Press Alt + F11 to open the VBA editor.
Insert a new module (Insert > Module) and paste the following code.
Customize the FindReplaceDict dictionary with your dynamic key-value pairs.
Run the macro (Alt + F8).

vba

CopyEdit

Sub DynamicFindAndReplace() Dim ws As Worksheet Dim FindReplaceDict As Object Dim key As Variant Dim cell As Range ' Create a dictionary for dynamic find-and-replace Set FindReplaceDict = CreateObject("Scripting.Dictionary") ' Add key-value pairs (find text as key, replace text as value) FindReplaceDict.Add "OldValue1", "NewValue1" FindReplaceDict.Add "OldValue2", "NewValue2" FindReplaceDict.Add "OldValue3", "NewValue3" ' Loop through all worksheets For Each ws In ThisWorkbook.Sheets ' Loop through each key in the dictionary For Each key In FindReplaceDict.Keys ' Replace text in the entire worksheet ws.Cells.Replace What:=key, Replacement:=FindReplaceDict(key), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Next key Next ws MsgBox "Dynamic find-and-replace completed!", vbInformation End Sub

Key Features:

Dynamic Dictionary: Add as many find-replace pairs as needed.
Sheet-wise Operation: It processes all worksheets in the workbook.
Flexible Matching: Uses partial or case-insensitive matching.

Example: Python for Dynamic Find and Replace

If you're working outside Excel, Python with pandas or regular expressions can be used for dynamic find-and-replace.

Let me know if you'd like the Python version or need customization!

Re: Dynamic Find and Replace in Macro

KieranRidge — Sun, 26 Jan 2025 22:27:18 GMT

Hi @sonalsharmaqueen - thanks but I am trying to do this in Alteryx, not Excel.