Way to remove special characters from a property

Started by EddieB, February 08, 2022, 09:08:27 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

EddieB

I have a shape that has a property that is a type string (Prop.Identifier) that I use to display text on a shape. I want to be able to remove any special characters a user tries to input. I was looking a the REPLACE function but not sure if it can be leveraged for this.

Is this the correct function to use? If not what is? Examples would be much appreciated. 

Also important to note: this also has to work with the LOWER and SUBSTITUE functions as I currently use those as well to remove whitespaces and change all text to lower case.

Yacine

#1

There are probably multiple ways you can approach the problem.


1. VBA
The most flexible would in my opinion to use a macro.
The macro would take the value of the field, "normalize" to your requirements and write the new value back in the field.
The macro is triggered by a user cell
CALLTHIS("normalize",,"Identifier")+DEPENDSON(Prop.Identifier)



Option Explicit


Sub normalize(shp As Shape, prop_name As String)
Dim temp As String
Dim num_lower_a As Integer
Dim num_zero As Integer
Dim c As String
Dim c_num As Integer
Dim i As Integer
Dim bValid As Boolean


    num_lower_a = Asc("a")
    num_zero = Asc("0")
    If shp.CellExists("prop." & prop_name, visExistsAnywhere) Then
        temp = shp.Cells("prop." & prop_name).ResultStr("")
       
        'first conversion'
        temp = LCase(temp)
       
        'iterate over the single characters'
        For i = 1 To Len(temp)
            c = Mid(temp, i, 1)
            c_num = Asc(c)
           
            'is c a lower case letter?'
            bValid = (c_num >= num_lower_a) And (c_num <= num_lower_a + 25)
            'if you were to allow numbers then:'
            bValid = bValid Or (c_num >= num_zero) And (c_num <= num_zero + 9)
           
            'Here you may add some special allowed characters'
            bValid = bValid Or c = " "
           
            If Not bValid Then
                temp = Replace(temp, c, "_")
                ' Note you can either replace non allowed chars by a special one (eg underscore) or a null string ("")'
            End If
        Next i
        shp.Cells("prop." & prop_name).Formula = Chr(34) & temp & Chr(34)
    End If
End Sub


2. Use shapesheet formulas
Well having really tested the above code, I realized how many checks you need to perform.


The idea was to use several in themselves embedded replace functions.
I won't dare writing this formula. Plus: the macro can be used for several different fields. In the shapesheet you would need to repeat the operation for each field you want to check.
Yacine

EddieB

Thanks Yacine for the suggestion! Unfortunately we have VBA disabled so I need to try to handle this in a shape sheet formula.  :(
I tried to use the substitute function and call out all the special characters (example below) however it seems it does not allow multiple substitutions.

=LOWER(SUBSTITUTE(Prop.Identifier," ", "","!", "","@", "","#", "","$", "","%", "","^", "","&", "")

Unless you know of a way to write multiple substitutions in the same formula?

Croc

Multiple substitutions is done something like this
=LOWER(SUBSTITUTE(SUBSTITUTE(Prop.identifier,"!","")," ",""))

Full formula
=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Prop.identifier," ",""),"!",""),"@",""),"#",""),"$",""),"%",""),"^",""),"&",""))

EddieB

Thanks Croc!! It will be lengthy but it will work!

Yacine

Quote from: EddieB on February 09, 2022, 02:33:40 PM
Unfortunately we have VBA disabled

A shame! You buy a Ferrari and get all gears above the first locked.
Yacine

Yacine

Thought about fooling Visio by an artificial for loop.
Visio didn't play my way.

What do you think?
Yacine

Paul Herber

Is this a situation where Python might play its part?
Electronic and Electrical engineering, business and software stencils for Visio -

https://www.paulherber.co.uk/

Yacine

Quote from: Paul Herber on February 09, 2022, 07:33:11 PM
Is this a situation where Python might play its part?

- The code would be leaner.
- I wouldn't know how to call a python script with CALLTHIS
- If VBA macros are not allowed, Python would be neither.
- But python could very easily build the formula for the shapesheet.
Yacine

Yacine

#9
Visio's formula has this (very useful) interpreter that detects endless loops and stops them.
In my previous post (the one with the screenshot), I tried to implement a loop with a set of "IFs" and "SetF(GetRefs)".
Sure it failed, but am I just missing a simple point or is it just impossible to implement.
This is obviously far beyond the topic of this post, but I felt too lazy to start a new topic.

So far as introduction. I would really appreciate reading the comments - and improvements - from the current "player" visioneers of the forum (I'm calling for Wapperdude, Visisthebest, Surrogate, ... and whom ever I forgot to mention (sorry) ).
Yacine

Croc

QuoteIn my previous post (the one with the screenshot), I tried to implement a loop with a set of "IFs" and "SetF(GetRefs)".
Sure it failed, but am I just missing a simple point or is it just impossible to implement.
Yacine, I've corrected your formulas a bit. Now they are working.
Thanks for the great idea :)


EddieB

#12
Quote from: Croc on February 10, 2022, 05:10:22 PM
And file


Thanks again Croc! Minor addition to take care of the upper to lower conversion.
LOWER(SUBSTITUTE(Prop.Identifier,User.I.Prompt,""))

Appreciate the help and ideas!!

EddieB

#13
Quote from: Croc on February 09, 2022, 03:08:21 PM
Multiple substitutions is done something like this
=LOWER(SUBSTITUTE(SUBSTITUTE(Prop.identifier,"!","")," ",""))

Full formula
=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Prop.identifier," ",""),"!",""),"@",""),"#",""),"$",""),"%",""),"^",""),"&",""))

Hi Croc,

For this solution that you previously mentioned, what is the escape character if I were to include "?

i.e. =LOWER(SUBSTITUTE(Prop.identifier,""",""),""","")

I have tried using the following all with no luck;

i.e. =LOWER(SUBSTITUTE(Prop.identifier,""",""),""","")
i.e. =LOWER(SUBSTITUTE(Prop.identifier,"""",""),""","")
i.e. =LOWER(SUBSTITUTE(Prop.identifier,""\",""),""","")


Croc

#14
You can use CHAR(34).
=LOWER(SUBSTITUTE(Prop.identifier,CHAR(34),""))
This will also work
=LOWER(SUBSTITUTE(Prop.identifier,"""",""))