Variable List Shape Data - any way to escape the ; character

[Visisthebest]

I was wondering what mechanism Visio uses to escape characters used for semicolon-separated lists.

When testing with variable lists in shape data, whatever I try:
;;
;;;
";"

it seems using a semicolon in a list item is not possible because there is no way to escape a semicolon in an item string.

Is this possible in any way?

Visio does allow to escape the double quotes " after all.

Thank you for sharing your insights in Visio!

[Paul Herber]

CHR(59)

[Visisthebest]

Super Paul thank you!

[Visisthebest]

If I create a list like this in the Format cell:

=";"&CHAR(59)&"AA;BB"

it still does not show a ; in the dropdown, it doesn't escape the ; character treats it as a separator still.

How to show a ; in a list item in a Shape Data dropdown.

[wapperdude]

Don't think it's possible.  But, I did not explore if Visio lets you define something other than semicolon as the separator.

[Visisthebest]

It is good to know that this is just a rule: no semicolon in Shape Data lists.

I don't think you can configure this for shape data lists or anything else where semicolons are used, probably too many diagrams would break if you configure another separator.

[Yacine]

A workaround: use another character and replace it by the actual semicolon when needed .
list = "a;b;|"
user.xy = replace(list,"|",";")

[Visisthebest]

Thank you Yacine!

[Surrogate]

user.xy = replace(list,"|",";")

IMHO there you must use SUBSTITUTE function with syntax

Code Select Expand

SUBSTITUTE (text,old_text,new_text,[start_num],[ignore_case_opt])
instead REPLACE with syntax

Code Select Expand

REPLACE (old_text,start_num,num_chars,new_text)
Sorry Visisthebest, i cant understand what is your issue and purpose !
When i use workaround by Yacine and formula ";"&CHAR(59)&"AA;BB", i get same result.

[Surrogate]

Thank you for sharing your insights in Visio!

Not Visio insight 
Just use symbol which looks like semicolon, for example Greek Question Mark

[Visisthebest]

Also a clever workaround Surrogate!

[wapperdude]

@Visisthebest:  Why do you want to do this anyway?  Seems that there's no real functionality, but rather, an intellectual curiosity.

[Visisthebest]

No there is an important reason and it has to do with the INDEX function used in the lookup lists.

I use Excel .XLSX files that have a specific format for creating diagram configuration data, users can upload these configurations in to Visio for the Shape Data lookups.

Now if a user changes the order of a list in Excel, then updates the Visio diagram, suddenly for instance, item 1 and 2 have been switched around.

This will lead to confusion as it seems there is a data entry error (that happened in the Visio shape data editor), but because shape data lists use the INDEX function the actual item chosen is not preserved, just its position in the list (which is now switched around).

This way, by storing every change a user makes (the actual item chosen, not just its INDEX position), I can see where things went haywire.

Users are of course instructed about this, but users are champions in not reading and/or forgetting instructions (I am also very good at this when it comes to someone else's product;). Better have a backup plan.

[wapperdude]

I understand the explained issue, but, not correlating it to the ";" issue.  But, that's OK.

Regarding the User following instructions issue, might you put a conditional statement to issue a warning by checking existing entry vs selected?...and ask user to proceed?  Might be a challenge for shapesheet only solution, but feels doable.  Obviously, could do it with code.

[Visisthebest]

You're right wapperdude that was actually about my question here, I answered it for the wrong question: http://visguy.com/vgforum/index.php?topic=9712.0

But also for escaping the semicolon character, I prefer not to have to say to customers you can't use a semicolon in any of the text for shape data lists, because the underlying software can't properly escape the semicolon character.

I will probably make the shape data lists editable only via Excel, not in shape data, and when the user updates Visio give a warning that all semicolons are automatically removed (if there are any), that makes sure the end users knows what's happening.