Multiple Replace

[Michael J Ross]

Hi,

I am currently working on a project where i need to try and compare a list of organisation names held by my organisation and another organisation (around 20000 records each).

To take into account differences in how the organisation names may have been entered I'm wanting to strip out things like ltd, limited, company, co, &, and , and so on. I've tried using IIf and replace which works ok if the organisation name only contains one of the substrings I want to replace but not if contains more than one.

How do i go about doing multiple replaces in the same string?

 

[Dennisk]

you need to use the INSTR Function in a loop as you cannot iterate within a IIF function. I presume you are running a query, hower iterations can only be executed in VBA.

 

[Michael J Ross]

Thanks, thought it would require VBA

 

[Brianwarnock]

Surely you only need a Function with a list of Replace actions , one for each item to be removed

fldText =Replace(fldText,"ltd","")
fldText =Replace(fldText,"limited","")
fldText =Replace(fldText,"plc","")
.
.
.
.


Brian

Edit The real problem when comparing text strings is nervous fingers adding extra spaces, I would remove all spaces and compare the resultant strings.

 

[Michael J Ross]

Thanks Brian,

That works (so simple, I was complicating it) and and thanks for the tip on spaces.