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.
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.
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?
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," ",""),"!",""),"@",""),"#",""),"$",""),"%",""),"^",""),"&",""))
Thanks Croc!! It will be lengthy but it will work!
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.
Thought about fooling Visio by an artificial for loop.
Visio didn't play my way.
What do you think?
Is this a situation where Python might play its part?
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.
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) ).
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 :)
And file
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!!
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,""\",""),""","")
You can use CHAR(34).
=LOWER(SUBSTITUTE(Prop.identifier,CHAR(34),""))
This will also work
=LOWER(SUBSTITUTE(Prop.identifier,"""",""))
To eliminate the double quote character, the following syntax works. It uses the CHAR() fcn to define the equivalent quote character:
SUBSTITUTE(User.TXT,CHAR(34),"")
So, if your text looked like: Now "IS" the time for change. It becomes: Now IS the time for change.