General

Re: How to translate double quotes to underscore?

GuyBoswell — Mon, 07 Sep 2015 16:08:39 GMT

I find these answers so helpful but the last little piece of the story doesn't get spelt out for simple people like me. I assume as there was no further conversation other people worked this out. It took me a while so I will explain my understanding.

The translate function uses three strings (pieces of text) separated with commas.

The first string is the string you want to translate - probably a reference to a field or another function acting on a field.

The second string is a list of all the characters you want to replace.

The third string is a list of the replacement characters.

Search the first string for characters in the second string. If you find any count how far you are in to the second string and count the same number of characters in to the third string and use that character instead.

so translate("horse","ehors","emous") returns mouse. h in horse is the second character in the second string so it is replaced with the second character of the third string which is m and so on.

If the string of replacement characters is shorter than the string of characters to replace any characters at the end of the second string are replaced with nothing at all.

The solution translate(string1,concat("£$%^",'"'),"_______") to replace special characters including double quote " works like this...

The second string is made up of two concatenated strings. First everything within the double quotes, i.e. £$%^' (which includes the single quote ') and then everything inside the single quotes, i.e. a double quote ". So the second string is the six characters £$%^'" or pound dollar percent hat single quote double quote. Because this is the output of the function concat() it doesn't need to be contained in quotation marks, either single or double, these can be assumed to exist.

The third string has the same number of characters (six underscores) as the second string. So every character in the second string can be replaced with the matching character from the third string.

i.e. you give me a piece of text stored in a location called string1 and I give you back the same piece of text but with any pound, dollar, percent, hat, single quote or double quote characters replaced with underscore characters

Re: How to translate double quotes to underscore?

DrumZHeaD — Tue, 06 Aug 2013 07:51:21 GMT

Awesome! I use the first solution and it completely works. I spent about couple days on this issue actually.

Thank you very much.

By the way, please allow me to ask some stupid questions. Although the formula works, I quite confuse about its function, so would you please explain some more detail about this formula?

Thanks,

One.

Re: How to translate double quotes to underscore?

Jimmy — Tue, 06 Aug 2013 07:08:45 GMT

Please give this a try:

translate(field1, concat(",:;'", '"'), "_____")

Alternatively, you can use your approach and have a reference field with all the escapable characters, but you should specify it like this:

translate(field1, reference, "__________")

In other words, reference should not be in quotes.

How to translate double quotes to underscore?

DrumZHeaD — Tue, 06 Aug 2013 04:46:19 GMT

I try using formula to replace special characters to underscore:

translate(field1, ",, :, ;, ', "", "_")

However, the formula contains error because of the double quotes.

Would anyone please tell how to escape the double quotes for InfoPath 2010?

I also try adding anoher field containing the double quotes as default value and add to the translate formula, but the result is all characters contained as the added field name are all eliminated.

Example: field name "reference" is assigned with double quotes " as a default value. If I enter text "car", the result is "a" (because it will eliminat all characters contained in the field name "reference")

Please help.

Thank you very much