Pre-sort before Substitute?

[comment]

OK, here's another suicide mission:

User inputs text into textfield. The text includes some user-defined labels.

User enters user-defined labels into a repeating field, say up to 10 labels.

User enters corresponding values into another repeating field.

Example:

textfield = "Please contact me at address."

labelfield[1] = "address"

valuefield[1] = "100 Rodeo Drive"

My job is to replace the labels with their corresponding values.

Easy, right? Nested Substitute(). But consider this:

textfield = "Please contact me at address, or at homeaddress."

labelfield[1] = "address"

labelfield[2] = "homeaddress"

or, at the user's whim:

labelfield[1] = "homeaddress"

labelfield[2] = "address"

So I have no idea in advance what the correct order of nesting should be.

If I get it wrong, I will end up with "home100 Rodeo Drive".

I need either some sort of pre-sorting, or at least a validation scheme that will prevent the user using labels that overlap.

[-Queue-]

What about something like

TrimAll(

Substitute( " " & TextField; [" " & LabelField; " " & ValueField]; [ " " & LabelField[2]; " " & ValueField[2]]; [" " & LabelField[3]; " " & ValueField[3]]; ... ; [" " & LabelField[N]; " " & ValueField[N]] );

0; 0 )

You could make it a Custom Function if it gets too long.

[comment]

I tried just the opposite of that, i.e. TextField & " ". Both aren't working, and I don't know why. It's trimming all the spaces instead (and I am not using Trim).

I don't know if it's relevant, but I am not actually inserting the values themselves. Instead I am inserting the field name, in preparation for Evaluate().

So, with labels "town" and "townhouse", this:

"I live in townhouse."

produces:

"IliveinDataR[1]house."

instead of:

"I live in DataR[2]."

What the...?

Could it be a bug in repeaters?

[-Queue-]

Weird. It worked for me earlier. Of course it only works when the overlap is at the end. So it would work for address and homeaddress but not town and townhouse.

Would it be possible to instruct your users to include < > or something similar around the 'variable' text? Then no overlaps would be an issue.

Oh, and I used " " & TextField due to the period after address.

[comment]

OK, the trim was my fault. Space & label was problematic with empty labels, so I added a IsEmpty() condition and forgot to add the alternative result.

But still no success on the task.

FWIW, this is my current, if you can spot something.

  

Substitute ( Calculation ;



[ " " & LabelR[1] ; Choose ( IsEmpty ( LabelR[1] ) ; " " & "DataR[1]"  ; " ") ] ;

[ " " & LabelR[2] ; Choose ( IsEmpty ( LabelR[2] ) ; " " & "DataR[2]" ; " " ) ] ;

[ " " & LabelR[3] ; Choose ( IsEmpty ( LabelR[3] ) ; " " & "DataR[3]" ; " " ) ] ;

[ " " & LabelR[4] ; Choose ( IsEmpty ( LabelR[4] ) ; " " & "DataR[4]" ; " " ) ] 

 ) 

[comment]

> it only works when the overlap is at the end

It must detect even pin vs. spinning. And I can't guarantee a space at the beginning or at the end - it can be in parentheses, etc.

> Would it be possible to instruct your users to include < > or something similar

Alas no. What a nightmare. Second time on the same file.

[comment]

I have made some progress by combining your method with mine, i.e. space before AND after, with a mile long substitution list to cater for all special cases.

However, I believe the proper solution woud be something along the lines of this .