Need a little guidance on how to strip quote marks from strings in a BASIC macro

[DracoHouston]

I am writing a little function to take an arbitrary string entered into a ‘town’ column in my spreadsheet, remove the special characters, force lower case. This is so I can compare it against known names and return a price in another function that calls this one.

I got it to strip everything but quotes, annoyingly, single quotes sometimes show up in the names I would be putting in that field. While the function is at a point where I can just not touch the quote mark keys when typing in the names, I’d love to know what I need to do to make it pick up the quote marks.

Here is the function, nothing fancy

Function NormalizeTownName(inZone As String) As String
	GlobalScope.BasicLibraries.LoadLibrary("Tools")

	Dim SafeZoneName As String
	SafeZoneName = LCase(inZone)
	SafeZoneName = DeleteStr(SafeZoneName, " ")
	SafeZoneName = DeleteStr(SafeZoneName, "`")
	SafeZoneName = DeleteStr(SafeZoneName, "~")
	SafeZoneName = DeleteStr(SafeZoneName, "!")
	SafeZoneName = DeleteStr(SafeZoneName, "@")
	SafeZoneName = DeleteStr(SafeZoneName, "#")
	SafeZoneName = DeleteStr(SafeZoneName, "$")
	SafeZoneName = DeleteStr(SafeZoneName, "%")
	SafeZoneName = DeleteStr(SafeZoneName, "^")
	SafeZoneName = DeleteStr(SafeZoneName, "&")
	SafeZoneName = DeleteStr(SafeZoneName, "*")
	SafeZoneName = DeleteStr(SafeZoneName, "(")
	SafeZoneName = DeleteStr(SafeZoneName, ")")
	SafeZoneName = DeleteStr(SafeZoneName, "-")
	SafeZoneName = DeleteStr(SafeZoneName, "_")
	SafeZoneName = DeleteStr(SafeZoneName, "+")
	SafeZoneName = DeleteStr(SafeZoneName, "=")
	SafeZoneName = DeleteStr(SafeZoneName, "[")
	SafeZoneName = DeleteStr(SafeZoneName, "{")
	SafeZoneName = DeleteStr(SafeZoneName, "]")
	SafeZoneName = DeleteStr(SafeZoneName, "}")
	SafeZoneName = DeleteStr(SafeZoneName, "|")
	SafeZoneName = DeleteStr(SafeZoneName, "\")
	SafeZoneName = DeleteStr(SafeZoneName, ":")
	SafeZoneName = DeleteStr(SafeZoneName, ";")
	SafeZoneName = DeleteStr(SafeZoneName, "'")
	SafeZoneName = DeleteStr(SafeZoneName, Chr(34))
	SafeZoneName = DeleteStr(SafeZoneName, ",")
	SafeZoneName = DeleteStr(SafeZoneName, ".")
	SafeZoneName = DeleteStr(SafeZoneName, "<")
	SafeZoneName = DeleteStr(SafeZoneName, ">")
	SafeZoneName = DeleteStr(SafeZoneName, "/")
	SafeZoneName = DeleteStr(SafeZoneName, "?")
	NormalizeTownName = SafeZoneName
	
End Function

As a test, I typed in one of the names and peppered it with garbage to see if it worked.

Input:

&%^&%.,,.<>/?;:^He”rvEy  `~‘’ “B|}{ay”!

Output:

he”rvey‘“bay”

[JohnSUN]

Welcome @DracoHouston !

Yes, your approach to the “throw away everything unnecessary” decision has a right to exist. But it seems to me that the “pick only what you need” approach can be implemented much easier. Indeed, if you list all unnecessary things one by one, you will have to write a lot. And if as a result you expect something like LCase (Regex()) which will return only the letters [a-z] (and the numbers [0-9]?), then it will be a little easier to write such string cleaning, right?

[DracoHouston]

An excellent suggestion. I am not that familiar with using regex so it had not occurred to me. Thank you.

[karolus]

Hallo

from string import punctuation


def clean_out( some_string ):
    
    punctuation_extended = f"{punctuation}{'„“«»”·’…©‚‘'}"
    repl = { char:None for char in punctuation_extended}    
    _repl_ = str.maketrans(repl)
    
    return some_string.translate(_repl_)

def main(*_):
    teststring = """&%^&%.,,.<>/?;:^He”rvEy  `~‘’ “B|}{ay”!"""
    print( clean_out(teststring))

main()
HervEy   Bay

Of course, @JohnSUN seems more straight, lets do:

import re

teststring = """&%^&%.,,.<>/?;:^He”rvEy  `~‘’ “B|}{ay”!"""

rex = re.compile(r'[^\d\w ]')

def main(*_):
    print(rex.sub('', teststring))
HervEy   Bay

[Lupp]

Names, whether names of persons or of “cities” or of … whatever are a mess. You may visit this page for explanations.
Anyway you may start with a “cleanup attempt”, but should try to do so without needing an UDF.
The REGEX() functioin, available in LibO since v 6.2 is a very powerful tool.
I would therefore start with what @JohnSUN suggested. One attempt is exemplified in the attached spreadsheet document.
disask95655suggestionForaKindOfCleanup.ods (15.1 KB)

[DracoHouston]

Thank you for both links.

[Lupp]

If you consider to use RegularExpressions to a greater extent, you may also be interested in the link to this good tutorial concerning RegEx.
The RegEx engine used by LibreOffice is the one provided by ICU and described here to more detail.