Workaround for unsupported Excel Substitute() function?

[Former Member]

I have a cell being analyzed which stores user input and after a button is clicked i need to analyze the text in that cell and replace every carriage return "char(10)" with the string "%0D". Excel's Substitute() function can do this in one shot for me, but it is not supported within Xcelsius. I have tried the Replace() function but this only swaps the first occurance and does not fix all of them if multiples exist.

I realize this can likely be done using an external script or .aspx but I was curious if it's possible to achieve this within the internal spreadsheet itself? What I have done is copied the Replace() formula into 5 cells, each one referencing the cell before it in hopes that there are never more than 5 carriage returns that need to be replaced, but I was wondering if there's a more efficient/automatic way to do this.

Considered posting this in an Excel forums but I have a feeling I would get a solution that involves another unsupported function. Thanks for any help/answers.

[Former Member · Accepted solution]

I don't know of any way to do this using only supported Excel formulas. The only ways I can think of would involve macros or an array formula.

However if this is still relating to the email link you were enquiring about I have 2 possible solutions / workarounds.

Solution 1:

1. Inputbox for user suggestion with Destination cell set to A1

2. A2 Contains the formula ="mailto:xATx.com?subject=Any Subject&body="&A1

3. URL Button links to cell A2 as the URL ensuring that "Encode URL" is checked

The above solution works correctly for me keeping all line breaks as they are typed. The "Encode URL" setting appears to be the key. This is using Xcelsius 2008 with Fix pack 3.3.

Solution 2:

A lot more complicated workaround more than a solution. This does effectively perform the same as the Substitute Function. I'll try to explain but without being able to attach an example my explanation may not make a great deal of sense.

1. A1 is the Destination for the user input or cell containing text with line breaks

2. Create an "input text area" object, check "enable HTML formatting" and set the default text as <BR>. Set the destination cell as A2 and check "insert data on load". Hide this item behind something so that it cannot be seen.

3. Create a push button which copies the value from cell A1 to cell A3

4. In cell A4 put the formula =NOT(ISERROR(FIND(A2,A3))) This returns TRUE if there are line breaks in the input text

5. In cell A5 put the formula =IF(A4,REPLACE(A3,FIND(A2,A3),1,"%0D"),IF(A3="","",A3)) This replaces the first line break with %0D if appropriate

6. Create a combo box component. Set the labels as cell A5, set the insertion type to label, set the destination to A3. Uncheck "Clear destination when no item is selected". Next on the behaviour tab set selected item type to Label and Item to Cell A5. This now copies the results of the formula back to Cell A3 if it changes.

7. If you now input any text in the input box and click on the push button both cells A3 and A5 will replace all occurrences of CHAR(10) with "%0D"

Personally I think this workaround may be a little tricky to get working exactly as you like... but it 'should' work.

I should note that this can be used as a substitute replacement by ignoring step 2 and instead typing the text to be replaced in cell A2 and the formula in cell A5 should be =IF(A4,REPLACE(A3,FIND(A2,A3),len(A2),"%0D"),IF(A3="","",A3)) where %0D is the text to replace with.

Hope this helps and makes sense,

Paul

[Former Member · Accepted solution]

Your second solution is what I came up with as well and did appear to be working correctly, but it seemed as if it didnt coincide with my version of Xcelsius. I installed FP3.5 this morning and now it works as simple as your 1st posting. I guess I need to be sure to stay current on new releases.

Thanks for all the help, appreciate it!