Substituting within only part of a text string

[Allegheny]

How to do I go from [text string] "Eng_question_q501" to [text string] "Eng_question_a501]?

I know how to use Substitute as a basic step, but how do I tell the calculation to only change the "q" if it is found within the final 5 characters?

Thanks!

Allegheny

[Lee Smith]

Hi Allegheny,

You are so close, didn't you try it this way?

Substitute(text string, "Eng_question_q501","Eng_question_a501")

should do it.

[Lee Smith]

Should have added, Replace "text string" with your field name that the string is in.

Lee

[Allegheny]

Oops, forgot to mention that I only offered an example of the data in those particular fields. There are more than 10,000 records, each with a different "q" number that needs to feed a different "q" number.

And Lee, how are things in the Big Tomato? I grew up in South Sac.

Allegheny

[Lee Smith]

If it is 10,000 records with the needed change all in one field, and they are all the same change, you can loop through the records using a loop and the Replace script step and this Substitute. I can help you with the script if that is what needs to be done???

Everything is Great in Woodland, but as you have hinted, it is tomato season, which means that there are Tomatoes are all over the road, they still don't cover the trailers for transporting the ******* things to the cannery.

HTH

Lee

[Allegheny]

Actually, what I have is this: FieldName"Question" has, in record 1 "Eng_punctuation_q501", in record 2 "Eng_punctuation_q502", in record 3 "Eng_punctuation_q503" and so on. I forgot when I started creating records and entering data that I also needed a FieldName"Answer" that follows a similar pattern (i.e., "Eng_punctuation_a501", "Eng_punctuation_a502", "Eng_punctuation_a501"). So I wanted to use a calculation to make data entry for FieldName"Answer" automatic whenever I fill FieldName"Question. I decided to use a simple Substitute to copy the contents and change each "q" to "a". But then I realized "q" may show up in other locations in some of the records (i.e., if the data were "Eng_question_q2601" or "Sci_quantum_q2601") and changing each "q" to "a" would yield, in those instances "Eng_auestion_a2601" or "Sci_auantum_a2601". See my problem? So what I really want is a caveat within my Substitute that says: only change the "q" to "a" if it is in the right-most five characters.

But I just realized something. Does using an underscore (_) tell Filemaker that I have three words in each text string? Perhaps there is a way to tell the Substitute to only work with the third word from the left (or first word from the right). Point is, I don't know how to create this secondary condition within my Substitute.

Ideas?

And I know what you mean about those pesky, slippery tomatoes. What a mess!

[Lee Smith]

Substitute(Answer, "punctuation_q", "punctuation_a"

[Lee Smith]

Needs a ) and the end.

Substitute(Answer, "punctuation_q", "punctuation_a")

[Allegheny]

But that doesn't take into account times when it isn't "punctuation_q" but rather "nouns_q" or "verbs_q". I like your idea about using "_q" and "_a", and using that alone ALMOST works. But I'm back to square one when the entire text string is something like "English_quotes_q947", in which instance I would get "English_auotes_a947" instead of the desired "English_quotes_a947".

I really think there has to be a way to combine the Substitute calculation with a Right calculation so it only looks at (and makes the substitution in) the final 5 characters.

Any other ideas out there?

[Lee Smith]

You are probably going to have to use the full prefex for each case such as the "punctuation_q" and "Quotes_q" . How many "punctuation_g" is there?

[Allegheny]

There has to be a better way to do it using a combination of Substitute and Right.

Anybody else out there have any ideas?

[andygaunt]

I've got one.

Substitute(

Question, Right(Question, Length(Question) - Position(Question, "_q", Length(Question), -1)), "a")

& TextToNum(RightWords(Question,1)

)

[Fitch]

Assuming that "q" is always followed by a number when it occurs at the end, but is never followed by a number otherwise -- this ain't pretty, but it should do it:

Substitute (Substitute (Substitute (Substitute (Substitute (

Substitute (Substitute (Substitute (Substitute (Substitute (

Question,

"q0", "a0"), "q1", "a1"), "q2", "a2"), "q3", "a3"), "q4", "a4"),

"q5", "a5"), "q6", "a6"), "q7", "a7"), "q8", "a8"), "q9", "a9")

[Jim McKee]

Hi Allegheny ...

If all you ever want to do is replace the "q" in that postion with an "a", then:

Replace(Question, Position(Question, "_", 1, 2) + 1, 1, "a")

In plain English, replace the first character after the second underscore with an "a"

If you need to sometimes change "q" to "a" and sometimes change "a" to "q", then use a case statement in the final argument:

Replace(Question, Position(Question, "_", 1, 2) + 1, 1, Case(Middle(Question, Position(Question, "_", 1, 2) + 1, 1) = "q", "a", "q"))

In plain English: replace the first character after the second underscore with an "a" if it's a "q", otherwise, replace it with a "q".

Good luck!

[Fenton]

Using global fields to set the parameters:

Replace(Question, Position(Question, _gSeparator, 1, _gOccurrence) + 1, 1, Case(Middle(Question, Position(Question, _gSeparator, 1,_gOccurrence) + 1, 1) = _gChar1, _gChar2, _gChar1))

And I shall call it "Jim's Tweedle"

[Ugo DI LUCA]

If the number is always at the end of your text field, why not :

Substitute(text,"_q" & TextToNum(text),"_a" & TextToNum(text))

[BobWeaver]

I throw my 2 cents worth in too.

If the one you want to replace is always preceded by an underscore, and there are no other q's preceded by an underscore, then:

Substitute(Text,"_q","_a")

Or:

If it is always the last "q" in the text, then you can use this formula:

Replace(Text,Position(Text,"q",1,PatternCount(Text,"q")),1,"a")

[Allegheny]

You were all very helpful. I ended up using Jim McKee's suggestion

Replace(Question, Position(Question, "_", 1, 2) + 1, 1, "a")

which I don't fully understand (need to give it a little time and thought) but which works beautifully!

Thanks

[Ugo DI LUCA]

Remember though that this calc replave the second "_".

So it won't work with engpunctuation_q501 which would turn angpunctuation_q501, nor with eng_punctuation_and_words_q501 which would stay inchanged.

So always keep your structure XXX_XXXX_QNumber

[Jim McKee]

Ugo ...

Yeah, I assumed that Allegheny will always use the concatenated key format that he presented in his question:

code_code_qnnn

My calc depends on his maintaining that format because it always replaces the 1st character after the second "_" (underscore).

Hopefully he understands that

[Ugo DI LUCA]

Just in case...