Author Topic: Way to remove special characters from a property  (Read 1427 times)

0 Members and 1 Guest are viewing this topic.

EddieB

  • Newbie
  • *
  • Posts: 5
Way to remove special characters from a property
« on: February 08, 2022, 04:08:27 PM »
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

  • Hero Member
  • *****
  • Posts: 2950
Re: Way to remove special characters from a property
« Reply #1 on: February 09, 2022, 04:56:48 AM »

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
Code
 CALLTHIS("normalize",,"Identifier")+DEPENDSON(Prop.Identifier) 

Code

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.
« Last Edit: February 09, 2022, 05:28:33 AM by Yacine »
Yacine

EddieB

  • Newbie
  • *
  • Posts: 5
Re: Way to remove special characters from a property
« Reply #2 on: February 09, 2022, 09:33:40 AM »
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

  • Sr. Member
  • ****
  • Posts: 311
    • Visioport
Re: Way to remove special characters from a property
« Reply #3 on: February 09, 2022, 10:08:21 AM »
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," ",""),"!",""),"@",""),"#",""),"$",""),"%",""),"^",""),"&",""))
« Last Edit: February 09, 2022, 10:12:35 AM by Croc »

EddieB

  • Newbie
  • *
  • Posts: 5
Re: Way to remove special characters from a property
« Reply #4 on: February 09, 2022, 10:32:33 AM »
Thanks Croc!! It will be lengthy but it will work!

Yacine

  • Hero Member
  • *****
  • Posts: 2950
Re: Way to remove special characters from a property
« Reply #5 on: February 09, 2022, 11:35:47 AM »
Unfortunately we have VBA disabled

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

Yacine

  • Hero Member
  • *****
  • Posts: 2950
Re: Way to remove special characters from a property
« Reply #6 on: February 09, 2022, 11:49:39 AM »
Thought about fooling Visio by an artificial for loop.
Visio didn't play my way.

What do you think?
Yacine

Paul Herber

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 3280
    • Paul Herber's website
Re: Way to remove special characters from a property
« Reply #7 on: February 09, 2022, 02:33:11 PM »
Is this a situation where Python might play its part?
Electronic and Electrical engineering, business and software stencils and applications for Visio -

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

Yacine

  • Hero Member
  • *****
  • Posts: 2950
Re: Way to remove special characters from a property
« Reply #8 on: February 10, 2022, 12:57:54 AM »
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

  • Hero Member
  • *****
  • Posts: 2950
Re: Way to remove special characters from a property
« Reply #9 on: February 10, 2022, 06:43:22 AM »
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) ).
« Last Edit: February 10, 2022, 06:49:54 AM by Yacine »
Yacine

Croc

  • Sr. Member
  • ****
  • Posts: 311
    • Visioport
Re: Way to remove special characters from a property
« Reply #10 on: February 10, 2022, 12:08:55 PM »
Quote
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.
Yacine, I've corrected your formulas a bit. Now they are working.
Thanks for the great idea :)

Croc

  • Sr. Member
  • ****
  • Posts: 311
    • Visioport
Re: Way to remove special characters from a property
« Reply #11 on: February 10, 2022, 12:10:22 PM »
And file

EddieB

  • Newbie
  • *
  • Posts: 5
Re: Way to remove special characters from a property
« Reply #12 on: February 10, 2022, 02:44:18 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!!
« Last Edit: February 10, 2022, 02:46:28 PM by EddieB »

EddieB

  • Newbie
  • *
  • Posts: 5
Re: Way to remove special characters from a property
« Reply #13 on: February 11, 2022, 10:17:39 AM »
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,""\",""),""","")

« Last Edit: February 11, 2022, 10:24:15 AM by EddieB »

Croc

  • Sr. Member
  • ****
  • Posts: 311
    • Visioport
Re: Way to remove special characters from a property
« Reply #14 on: February 11, 2022, 11:48:31 AM »
You can use CHAR(34).
Code
=LOWER(SUBSTITUTE(Prop.identifier,CHAR(34),""))
This will also work
Code
=LOWER(SUBSTITUTE(Prop.identifier,"""",""))
« Last Edit: February 11, 2022, 11:56:52 AM by Croc »