Find position of text string in larger string

Question

I have a long string in my Services table, the result of a Combined Text Summary field, each element takes the pattern V###-#### (where the number parts of the string are arbitrary length). For example :
V102-136 ; V23-4072 ; V8-25
I want to create a formula which extracts correct element in this array, based on the value in another field, and I could do it if I knew the position in the text string of the "V" value. So if there were a function Find which returned the position of a text string, that would do it. Something like
Find([Summary Field", "V" &amp; [Text string to search for])
I can't find a function to do this. Functions like left just search for single characters, and I need to search for a longer string. Ideas?------------------------------Jonathan Heuer------------------------------

markshnier__you · Answer

I would approach this differently. I would use the part function in QuickBase to isolate the different elements and then use a case function to determine the result.var text value = totext([my multi select field]);var text A = Trim(Part($value,1,";"));var text B =Trim(Part($value,2,";"));var text C = Trim(Part($value,3,";"));etcvar text Z = Trim(Part($value,26,";"));Case([value in another field]),1, "$A,2, $B,3, $C,etc
26, $Z)------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

neilschneider · Answer

If you only want to know if the summary field contains the value you are looking for (and don't need the index in the array), this should do it..
var text data = "V102-136 ; V23-4072 ; V8-25";
var textList dataList = Split($data, ";");
var text field = "V23-4072";
var bool hasField = Contains($dataList, $field);

If ($hasField, $field, "notfound")

------------------------------
Neil Schneider
------------------------------

jonathanheuer · Answer

Thanks Mark, I like this approach. The problem is that the length of the array in the summary text field is arbitrarily long; that is, it may well have more than 26 elements in it.

------------------------------
Jonathan Heuer
------------------------------

jonathanheuer · Answer

Thanks Neil- yep I know about Contains. The problem is I need to extract the actual value from the text string, not just whether it's there or not.

------------------------------
Jonathan Heuer
------------------------------

markshnier__you · Answer

Well there is no limit to the length of a formula

var text AA = Trim(Part($value,27,";"));
var text AB =Trim(Part($value,28,";"));
var text AC = Trim(Part($value,29,";"));
etc.

just make the formula longer, for the largest number of practical elements, say 100? Whatever, it's just copy and paste to make the formula longer.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

Forum Discussion

Find position of text string in larger string

10 Replies

Related Content

Assistance Needed to Locate Formula Fields which include Specific String

Strings and Integers in a Pipeline Date Calculations

URL String Reference

Sorting a date range as string

Overdue Duration Formula most be positive # of Days

Recent Discussions

Current URL Display without JavaScript

Remove the save button when from new forms format when applying I-forms (IFV=1) #354382

Help Needed with Quickbase Pipeline to Accumulate Values Instead of Overwriting

APP - Communications

Kanban WIP Limits