Is there a formula that will tell tell you the position of a specficic character in a text string

Question

I'm looking for the equivalent of the "Find" function in excel.

find("x","I am x years old") would equal return 6. Since x is the 6th character in the string. I don't think this formula exists in quickbase, but any work arounds?

With this formula I can then add logic for left/notleft/right/mid to extract specific text elements.

quickbasecoachd · Answer

Try this. I will use formula variables to break the formula into smaller pieces.var text Temp = Left([my text field],"x");Length($Temp) +1

quickbasecoachd · Answer

Note that if your goal is to parse out a section of text up to a character, that the Left and Right functions in Quickbase will do that for you. You can do this

Left([My string],6)

But as you can see above, you can also do Left([My String],"x") which gives all the text up to the first x.

jan-willemspeck · Answer

I would like to go one step further with this function.

1) find a specific chararter combination
ie
var text Temp = Left([my text field],"#%");
Length($Temp) +1
2) from the position where "#%" is located I like to read the next 5 characters
ie Mid([field], <start position>, 5)

if I replace <start position> with the two lines in 1) it doesn't work. I assume it has the do with the variable, the code in 1) or with the ;
Is there a better way to get this?

In an even better scenarion I should perhaps first check if the text string contains the value "#%" at all to avoid wrong output of 1). Currently if it can't find it seems to result in the sting length

quickbasecoachd · Answer

did you try thisvar text Temp = Left([my text field],"#%");var number StartPosition = Length($Temp) +1;Mid([field], $StartPosition, 5)

jan-willemspeck · Answer

No I didn't.But what I learn from this is how to define and use variables and that you can treat the formula field also as a field where we can enter pieces of code because that is what you actually doing.And that opens new possibities, lots of themWhat a joy :-) for the second time today TXS!!

Forum Discussion

Is there a formula that will tell tell you the position of a specficic character in a text string

Related Content

Help extracting text between unique character

Extract Text Between 2 special characters

how to count the number of specific characters

Formula checkbox for certain entries over a character limit

Extract characters